I have a string that I want to parse. The seperator is the >
What is the best way to parse these into numbers?
R-P312>Z290>L21>S552>DF13>ZZ10_1>Z16423>Z255>L159>Z16429>BY519>ZZ7_1>BY17700>Y93172
[Re-Titled By Moderator]
iMac 27″, macOS 12.7
If that is in cell B2 and you want to extract only the numbers to C2, D2, etc.
C2 =VALUE(REGEX.EXTRACT($B2,"\d+",COLUMN()−2))
Fill right
This assumes they will all be as shown in your string: whole numbers, no decimals, no thousands separators, no spaces between digits, etc. 10_1 will be a 10 and a 1. Two separate numbers. Same with 7_1. If you want a different result, say what it is. The "_" can be included as a character to be extracted along with the numbers if you want 10_1 (which is not a "number"). Or we can go one more step and turn it into 10.1
VALUE converts them from strings into actual numbers. Leading zeros, if any, will disappear. You don't need to use VALUE but, as strings, they will be ignored if used in any numeric functions (like SUM). You can use the math operators +-*/ but not the numeric functions.
If that is in cell B2 and you want to extract only the numbers to C2, D2, etc.
C2 =VALUE(REGEX.EXTRACT($B2,"\d+",COLUMN()−2))
Fill right
This assumes they will all be as shown in your string: whole numbers, no decimals, no thousands separators, no spaces between digits, etc. 10_1 will be a 10 and a 1. Two separate numbers. Same with 7_1. If you want a different result, say what it is. The "_" can be included as a character to be extracted along with the numbers if you want 10_1 (which is not a "number"). Or we can go one more step and turn it into 10.1
VALUE converts them from strings into actual numbers. Leading zeros, if any, will disappear. You don't need to use VALUE but, as strings, they will be ignored if used in any numeric functions (like SUM). You can use the math operators +-*/ but not the numeric functions.
Now that I know what you want (everything between the >'s, not just the numbers), we can do it easiest with the TEXTBETWEEN function.
C2 =IFERROR(TEXTBETWEEN(">"&$B2&">",">",">",COLUMN()−2),"")
Fill right
I wrapped it in IFERROR in case these strings to be parsed are of different lengths. When TEXTBETWEEN runs out of matches, it throws an error. If they will all have the same number of items then you can leave that part off.
=REGEX.EXTRACT($B2,"[0-9_]+",COLUMN()−2)
This will extract the "numbers" with 10_1 being extracted as 10_1. All the results will be strings. I am assuming there are no other "_" characters in your long strings. If there are, those "_" characters will get extracted also. But that can be dealt with, too, with some more complex coding.
Ah, I believe you posted a 'text edit' option a long while back in another post on this subject.
Steps:
*How do we specify a tab without the cut and paste option?
This is messy, but it should work. Why does Apple make this so hard?
Row 1: sequential numbers 1,2,3
Row 2: length of string in cell to lower left =LEN(B4)
Row 3: Find the starting position of delimiter less 1 SEARCH(">",B4,start-pos)
Cell B4: Text to be parsed.
Row 4: Get the mid value of the string after the delimiter. MID(B4,SEARCH(">",B4)+1,C$2−C$3)
Cell A5: Count number of delimiters: COUNTMATCHES(B4,">")
Row 5: Get the left value of the cell. Use if statement to get the final cell IF(B$1>$A5,B4,LEFT(B4,C$3−1))
No, we need all the text. As a string.
This will count the number of seperators in the cell.
COUNTMATCHES(Z46516 Peter Millsap,(REGEX(">")))
And this will work to get the word between the first and second delimiter
TEXTBETWEEN(a1,REGEX(">",1),REGEX(">",2))
And of course the 'paste formula results' is hidden. Have to go to the main menu. or shift command V
How can I parse strings in Numbers on Mac?
