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.

 

[Rick Rothstein]

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

Put these formulas in the indicated cells and copy them down (you can safely copy them down further than there is data to fill them, the excess will display the empty string "" in the cell)...

B1: =IFERROR(MID($A$1,FIND("|",SUBSTITUTE($A$1," =","|",ROWS($1:1)))-2,2),"")

C1: =IFERROR(MID($A$1,FIND("|",SUBSTITUTE($A$1,"#","|",ROWS($1:1)))+1,3),"")

D1: =IFERROR(MID($A$1,FIND("|",SUBSTITUTE($A$1,"Task ","|",ROWS($1:1)))+5,6),"")

 

[buteaur]

Wow... I really cannot thank you enough. I have thousands of lines and this saved me hours upon hours.

I am always amazed by the help i recieve on this board.

Thank you again.

 

[Jonmo1]

B1: =IFERROR(MID($A$1,FIND("|",SUBSTITUTE($A$1," =","|",ROWS($1:1)))-2,2),"")

C1: =IFERROR(MID($A$1,FIND("|",SUBSTITUTE($A$1,"#","|",ROWS($1:1)))+1,3),"")

D1: =IFERROR(MID($A$1,FIND("|",SUBSTITUTE($A$1,"Task ","|",ROWS($1:1)))+5,6),"")

Nice!
Like #2100

 

[Rick Rothstein]

Nice!

Thanks!

Like #2100

@Jonmo1 -

@Everyone Else - It's an inside joke.

 

[buteaur]

i hate to come back on this one. I used the formulas when I you originally posted the reply and I saw that it works perfect. However, today I was able to sit down to work on this and I'm wondering, how do I get this to carry on to the next cell of data? (A2).

Thank you .

 

[Rick Rothstein]

i hate to come back on this one. I used the formulas when I you originally posted the reply and I saw that it works perfect. However, today I was able to sit down to work on this and I'm wondering, how do I get this to carry on to the next cell of data? (A2).

A couple of questions...

1) So the split out values for the text in cell A2 will start at Row 6 then?

2) Will you always have exactly 5 lines of text in each cell?

 

[buteaur]

Correct. It will start in row 6. I've actually created it so all data will start 7 rows apart. I tweaked the formula to account for that. I just need to figure this part out.

 

[Rick Rothstein]

I've actually created it so all data will start 7 rows apart.

I don't understand what you mean by this... can you please explain it in a little more detail (maybe post a picture as well)?

I tweaked the formula to account for that.

Can you please show us your tweaked formula (I think that, coupled with the explanation you give to the above question, will help me understand what you want).

 

[buteaur]

The next cell of data, for the entire spreadsheet, is seperated by 6 7 rows. you can disregard my "tweaked" comment. That wasn't accurate.

Thanks!

 

[Rick Rothstein]

The next cell of data, for the entire spreadsheet, is seperated by 6 7 rows. you can disregard my "tweaked" comment. That wasn't accurate.

So, is your original data in cells A1, A2, A3, etc. (which is what Message #15 says) or in A1, A7, A13, etc. (as I think Message #19 is suggesting, although I am not exactly sure what "6 7" means)? Please clarify and, when you do, keep in mind that while your layout is visible and obvious to you, it is not obvious to us... we only have your posted messages to tell us what your data is and how it is laid out.