Text To Column

Margaret Walker

I need help taking this string of text and creating column for each unique number sequence that is separated by comma.

I was able to do the first column, but I'm unable to figure out the rest. Any help is appreciated.

KPH

Hi @Margaret Walker

You can use a combination of MID, FIND and SUBSTITUTE functions.

You use the SUBSTITUTE to change the nth comma into another symbol (something that isn't otherwise in your string).

Then you use FIND to find that other symbol.

Then you use MID in a similar way to how you used LEFT, to extract just the part you want. I suggest using FIND and SUBSTITUTE to identify both the start position and the length of the string to extract as even though your numbers seem to all be 6 digits some have preceding spaces and others do not. The slightly more complex formula will be more reliable. However there is a caveat at the end.

For column 2 the formula would look something like

=MID($[Req ID]@row, FIND("*", SUBSTITUTE($[Req ID]@row, ",", "*", 1)) + 1, FIND("*", SUBSTITUTE($[Req ID]@row, ",", "*", 2)) - (FIND("*", SUBSTITUTE($[Req ID]@row, ",", "*", 1)) + 1))

Column 3 is the same but incrementing the numbers in bold as you now want to start at the 2nd comma and find the length of the string between the 3rd and 2nd commas.

=MID($[Req ID]@row, FIND("*", SUBSTITUTE($[Req ID]@row, ",", "*", 2)) + 1, FIND("*", SUBSTITUTE($[Req ID]@row, ",", "*", 3)) - (FIND("*", SUBSTITUTE($[Req ID]@row, ",", "*", 2)) + 1))

Column 4 is

=MID($[Req ID]@row, FIND("*", SUBSTITUTE($[Req ID]@row, ",", "*", 3)) + 1, FIND("*", SUBSTITUTE($[Req ID]@row, ",", "*", 4)) - (FIND("*", SUBSTITUTE($[Req ID]@row, ",", "*", 3)) + 1))

However, if you want to find the last string, you will need something else. The last ID does not seem to have a comma after it. So this formula won't be able to calculate the length and will return an error.

Do you need to extract them all, or are you just looking for the first three? I don't want to complicate things unnecessarily.

Margaret Walker

Thanks so much! I was able to create columns for each unique ID, but how do I get my vlookup to work? It's saying "no match" but the unique id is on my other sheet. Is it because it's a formula?

Michelle Choate 2

Just a side note here - not answering your formula question. It is best practice to name your references. So rather than "R7 User Requirements Import Range 1", I would name it something like "R7 User Requirements Unique ID" or something similar so when you have 90+ references in a sheet, you know what they are looking at, rather than "Range 1".

And I also prefer INDEX/MATCH to a VLOOKUP because then the column can move anywhere in the sheet and my formulas do not get broken. :) Just some helpful tips I was given when I started using Smartsheet.

Georgie

Hi @Margaret Walker,

The reason you’re getting the #NO MATCH error is because your =LEFT([Req ID]@row, 6) formula is converting the number value from the “Req ID” column into a text string. So, when the VLOOKUP is looking for matches between the text value in your “1” column and the numbers in the ID column on your referenced sheet, it can’t find a match, because one column contains number values and the other column contains text values that represent numbers.

To get around this, you can use the VALUE function to convert the values in your “1” column from text strings to numbers, so instead of using =LEFT([Req ID]@row, 6), you can use =VALUE(LEFT([Req ID]@row, 6)). Once the values in your “1” column are numbers, your VLOOKUP should then work as expected. More on the VALUE function can be found here: VALUE Function.

To add to Michelle’s comment, I’d also recommend using INDEX(MATCH) formulas in place of VLOOKUP formulas, for the reason Michelle has provided - if columns in your lookup table are ever moved, or another column is inserted within that lookup table, this will change the lookup table and your formulas will return different values, whereas INDEX(MATCH) will always return values from the selected column. For more information about INDEX(MATCH) formulas, take a look at this help article: Formula combinations for cross sheet references.

Hope that helps!

Georgie

KPH

Hi @Margaret Walker - I'm glad those formulas solved your problem and apologies for not spotting your follow-up question, but looks like @Georgie has sorted you out. Have a good weekend!