0

I am trying to extract numbers from a comma delimited list using excel function

The data looks like this:

1,58,,Private Information #1,After 59 seconds, your private estimate is that the final value will be $44.17

if the above data are in cell A1, I want to retrieve 59 in cell B1 and 44.17 in cell C1. The #of digits of a number might vary. Only numbers in the 5th and 6th comma-separated field are needed, and there will only be one number in each of those fields. For the 5th segment, the # will always be surrounded by the same text( i.e., After xxx seconds). For the 6th segment, it will always be the final characters, and they always follow a single $

I am not familiar with excel and would appreciate any help! Thanks in advance.

6
  • Look at the Text to Columns function from the menu. Note that it uses the source cell as the first output cell, so copy you source data to a scratch area and do it there.
    – fixer1234
    Feb 24, 2019 at 3:32
  • You’ve given us one example, and not explained how it works.   Do you want B1 to get the seventh and eighth characters in the fifth comma-separated field in A1, or the second word in that field?   Do you want C1 to get the last five characters in A1, or the last five characters in the sixth field, or the 57th-61st characters in that field?   Do you want B1 and C1 to get the fourth and fifth numbers in A1 (ignoring the commas)?   Or do you want to extract all the numbers in the line (in which case, why not 1, 58 and 1)? Feb 24, 2019 at 4:18
  • Thank you for your response and sorry for the confusion. Only numbers in the 4th and 5th comma-separated field are needed, and there will only be one number in each of those fields Feb 24, 2019 at 4:30
  • 1. It looks like the 5th and 6th segments contain the info you describe rather than 4th and 5th? 2. How uniform is the text in those segments from record to record? 3. Will the 1st target (59) always be surrounded by the identical text, or how can it be identified (e.g., the only digits in otherwise random text in that segment, always the 2nd "word" in that segment, etc.)? 4. Will the 2nd target (44.17) always be the final characters in the record? Will they always follow a $? Will that $ always be the only $ in the record, or at least in the last segment?
    – fixer1234
    Feb 24, 2019 at 5:09
  • My bad. it is indeed 5th and 6th segment. For the 5th segment, the # will always be surrounded by the same text( i.e., After xxx seconds). For the 6th segment, it will always be the final characters, and they always follow a single $. Feb 24, 2019 at 5:18

2 Answers 2

1

The formula for C1:

=VALUE(MID(A1,SEARCH("$",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+1,LEN(A1)))

Assumes there is only one dollar sign in the sixth segment.
The bar symbol can be changed to any character guaranteed to not be in the text.
MID allows for more characters to be returned than exist (simply returns the rest of the text; a lazy RIGHT, instead of calculating the number of characters to return, the start location is used).

The formula for B1:

=VALUE(MID(A1,SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5,SEARCH("second",A1,SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5)-(SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5)))

B1 simplified with a helper Cell (D1):

D1=SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5 B1=VALUE(MID(A1,D1,SEARCH("second",A1,D1)-D1))

2
  • the formula you have suggested are not intelligent enough, how they work if Text you have used AFTER & SECOND will change !! Feb 25, 2019 at 8:42
  • @RajeshS The OP stated For the 5th segment, the # will always be surrounded by the same text( i.e., After xxx seconds).
    – Ted D.
    Feb 25, 2019 at 17:36
-1

To get 59 and 44.17, I would like to suggest combination of UDF (VBA Macro) & a Formula.

enter image description here

How it works:

  • Write the text in A1.
  • Copy & Paste this VBA code as Module with the Sheet.

    Function INDEXLIST(strList As String, strSeparator As String, lngIndex As Long) As String
    
    Dim ListArray() As String
    ListArray() = Split(strList, strSeparator)
    INDEXLIST = ListArray(lngIndex - 1)
    
    End Function
    
  • In B1 & B2 write search position for 59 & 44.17 are 5th & 6th.

  • Write this Formula in C1 and fill it down.

=INDEXLIST(A1,",",B1)

  • Finally enter this Formula in D1 and fill it down.

    =LOOKUP(9.9E+307,--LEFT(MID(C1,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $C1&"1023456789")),999),ROW(INDIRECT("1:999"))))
    
  • You get 59 & 44.17.

N.B.

  • 9.9E+307 is the largest number excel can store in a cell. Often referred as Biggest Number.

  • LOOKUP tries to find the Largest value that is less than or equal to the Lookup value. If the lookup value is 9.9E+307, then it's not possible to have number larger than 9.9E+307 in the range, therefor the Lookup will find the largest value in the range.

    FIND({1,2,3,4,5,6,7,8,9,0}

  • The FIND function returns the position of the Data when it's found in the Range that match the array of values returned by the ROW function {1,2,3,4,5,6,7,8,9,0}.

  • 999 set max 3 digits and Row(INDIRECT("1:999")` refers the Data Cell.

  • Adjust cell references in the Formula as needed.

1
  • Write your concern why this has been down voted,, help me to put my view !! Feb 27, 2019 at 5:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .