Extract two numbers out of a string in Excel

Question

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.

do those strings always start with ID:? how about MID(G3,3,FIND(" ", g3)) — Marc B
– Marc B, Commented Feb 19, 2016 at 17:41
Is ID:1234567 RXN:89012345 in the same cell? You could also do a Data --> Text to Columns, using a space delimiter to separate the two. To get rid of the "ID" and "RXN", see @ML's answer. — BruceWayne
– BruceWayne, Commented Feb 19, 2016 at 17:45
Marc - The data does always start with ID:, however your function produces the result ":1234567 RX" and ":12345 RX". — Fly Guy
– Fly Guy, Commented Feb 19, 2016 at 18:07
If you're doing a macro anyway why not use the VBA method of split? You could split by : and doing so would give you an array that you would then pull your number out of. — Kyrubas
– Kyrubas, Commented Feb 19, 2016 at 18:20

M.L · Accepted Answer · 2016-02-19 17:45:42Z

1

A way to do this is:

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
Go to the Data tab, Text to columns
Choose Delimited>>Next>> choose Space here, in Other, type a colon(:) >> Finish
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.
Delete unwanted columns

answered Feb 19, 2016 at 17:45

M.L

3282 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fly Guy Over a year ago

Sorry, but I should have mentioned that I do need a formula as I will be using a macro to apply to many spreadsheets at once.

Gary's Student · Accepted Answer · 2016-02-19 18:31:07Z

1

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:

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

answered Feb 19, 2016 at 18:31

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

Ron Rosenfeld · Accepted Answer · 2016-02-19 22:18:10Z

0

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.

answered Feb 19, 2016 at 22:18

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Collectives™ on Stack Overflow

Extract two numbers out of a string in Excel

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related