Extract data into separate cells

[buteaur]

Hello.

I have data in a cell that I need to extract into seperate cells. Here's the example:

14 = Street RCA Combo #203: Task BG0178
15 = Street RCA Combo #204: Task BG0018
16 = Street RCA Combo #205: Task BG0020
17 = Street RCA Combo #206: Task BG0022
18 = Street RCA Combo #207: Task BG0348

The issue I'm having is getting these to be extracted properly into their own cells.

I need the first number to get into it's own cell, I need the number after "combo" to be in it's own cell and then I need the last number (BGxxxx" to be in it's own cell.

I can't figure it out.... Any help is appreciated.

 

[Jonmo1]

Try these..

Note, the formula in C is dependant on the results of B and D.

Unknown
	A	B	C	D
1	14 = Street RCA Combo #203: Task BG0178	14	203	BG0178
2	15 = Street RCA Combo #204: Task BG0018	15	204	BG0018
3	16 = Street RCA Combo #205: Task BG0020	16	205	BG0020
4	17 = Street RCA Combo #206: Task BG0022	17	206	BG0022
5	18 = Street RCA Combo #207: Task BG0348	18	207	BG0348
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(" ",A1))+0
C1	=SUBSTITUTE(SUBSTITUTE(A1,B1&" = Street RCA Combo #",""),": Task "&D1,"")+0
D1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

 

[buteaur]

I just want to be sure I stated this correctly and that this will work.

The example I provided has all that data put into one cell. So it's all in A1. That's what's making it difficult for me.

Thank you!

 

[Jonmo1]

Yep, that's how it is in my example as well.

 

[Rick Rothstein]

The example I provided has all that data put into one cell. So it's all in A1. That's what's making it difficult for me.

Some questions then...

1) Is that first number always two digits long?

2) Is the number after "Combo#" always three digits long?

3) Is that number after "Task" always six characters long?

4) Where did you want the output to go to... B1:D5 or A2:C6?

 

[Rick Rothstein]

Yep, that's how it is in my example as well.

I read Message #3 as meaning all six posted lines are in Cell A1, not A1:A6.

 

[Jonmo1]

I read Message #3 as meaning all six posted lines are in Cell A1, not A1:A6.

I thought my statement that the formula in C depends on B and D prompted that clarification..

 

[Jonmo1]

@buteaur
Can you clarify..
Did you mean that ALL 5 lines are in one cell (A1) ?

or

A1 = "14 = Street RCA Combo #203: Task BG0178"
A2 = "15 = Street RCA Combo #204: Task BG0018"
A3 = "16 = Street RCA Combo #205: Task BG0020"
Etc ?

 

[Rick Rothstein]

@buteaur
Can you clarify..
Did you mean that ALL 5 lines are in one cell (A1) ?

or

A1 = "14 = Street RCA Combo #203: Task BG0178"
A2 = "15 = Street RCA Combo #204: Task BG0018"
A3 = "16 = Street RCA Combo #205: Task BG0020"
Etc ?

And after you answer the above question that Jonmo1 asked, please answer the questions I asked in Message #5 as well.

 

[buteaur]

Hello. Yes, all 5 lines are in one cell. And yes, the length of the numbers is always the same.

Thanks you again.