Need to add a space every few characters

Options

I am using a form that is turning individual amounts into a long string of numbers.

For instance, information from multiple lines on a separate smartsheet are being entered into a form like this:

2750

2750

3500

But the column created is now showing 275027503500

How do I make a formula to add in a space every four digits? I want it to look like 2750 2750 3500

I've tried changing the form from multiple to single line, but always get the same result.

Thanks!

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    Hi Misha,


    You can use " " as an item. This designates that you want to add a space. For example:

    =[Column2]@row + " " + [Column3]@row + " " + [Column4]@row


    Hope this helps!



    Best,

    Heather

  • Misha A
    Misha A ✭✭✭
    Options

    Hi Heather, thank you so much for your fast response. Unfortunately, I'm not sure it'll work with this situation. I want the form user to be able to copy the multiple numbers into a single form field, and not individual fields/columns for each number.

    I've attached an example of the source spreadsheet and the document request that gets created from the form. Team members copy the information from the columns into a form which triggers a request to make a document. When columns are pasted into the form, a space is automatically added between the entries for the start/end dates, venue, etc. But it's not adding it for the "Fee for Bulk" column. I just get a long string of numbers. I don't want to ask the team member to copy/paste each individual number into an individual form field.

    Source Spreadsheet

    Document request from form


    I'm having to add in the spaces manually. Any ideas for a formula to add in the spaces into this column?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    How exactly are users entering the multiple numbers into the form in a single form, and how exactly are you pulling those numbers into the other sheet?

  • Misha A
    Misha A ✭✭✭
    Options

    Hi Paul!

    Thanks for your help! The numbers are being copy/pasted into the form as a whole unit. I've tried setting this field in the form to be single line or multi-line, but get the same results. The form feeds into a sheet that generates a document.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Misha A

    I hope you're well and safe!

    Will it always be 4 sets of numbers?

    You could use the functions LEFT, MID, and/or RIGHT to parse them out to so-called helper columns and join them together with JOIN and use the CHAR(10) as the separator.

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic day!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Misha A
    Misha A ✭✭✭
    Options

    HI @Andrée Starå Thanks for you help! However, the form may not always be 4 sets of numbers. The minimum is two sets (8 characters), but could be as many as 10 or 15 sets. But each set of number is always 4 digits long, hence needing to add in the space every 4 characters.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @Misha A

    Happy to help!

    It will still work with my method as long as you add as many helper columns that are needed.

    Make sense?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If they are entering as in your screenshot, try enabling text wrap on the column.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!