CODEWITHSUNDEEP
excel
Fly Guy
Fly Guy

Reputation: 265

Extract two numbers out of a string in Excel

I have a string that I need two numbers extracted and separated into two columns like this.

ID:1234567 RXN:89012345
ID:12345 RXN:678901

Column 1   Column 2
1234567    89012345
12345      678901

The numbers can be varying number of characters. I was able to get column 2 number by using the following function:

=RIGHT(G3,FIND("RXN:",G3)-5)

However, I'm having a hard time getting the ID number separated.

Also, I need this to be a function as I will be using a macro to use over many spreadsheets.

Upvotes: 0

Views: 6898

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

If your format is always as you show it,then:

B1:  =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ",REPT(" ",99)),":",REPT(" ",99)),99,99))

C1:  =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ",REPT(" ",99)),":",REPT(" ",99)),3*99,99))

We substitute a long string of spaces for the space and : in the original string. Then we extract the 2nd and 4th items and trim off the extra spaces.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

With data in column A, in B1 enter:

=MID(A1,FIND("ID:",A1)+LEN("ID:"),FIND(" ",A1,FIND("ID:",A1)+LEN("ID:"))-FIND("ID:",A1)-LEN("ID:"))

and copy down. In C1 enter:

=MID(A1,FIND("RXN:",A1)+LEN("RXN:"),9999)

and copy down:

enter image description here

The column B formulas are a pretty standard way to capture a sub-string encapsulated by two other sub-strings.

Upvotes: 1

M.L
M.L

Reputation: 328

A way to do this is:

  1. Select all your data - assuming it is in a string all the time - which means one cell has one row with ID&RXN nos. So if you have 100 rows such data, select all of it
  2. Go to the Data tab, Text to columns
  3. Choose Delimited>>Next>> choose Space here, in Other, type a colon(:) >> Finish
  4. You will get "ID" in first column, every cell; ID no in second column every cell; RXN in third column every cell and RXN no in 4th column every cell.
  5. Delete unwanted columns

Upvotes: 1

Related Questions