Finding Text/Number Combinations w/in Text String

[cjvenables]

Hello,

I need to find a way to pull text/numbers together out of a cell.

This is my text in cell M1:

000184.00@000040.99:CC#008661 FSR#5108434 PROVIDE

This is how I would like it to appear in my spreadsheet:

M1: 000184.00@000040.99:CC#008661 FSR#5108434 PROVIDE
N1: CC#008661
O1: FSR#5108434

Notes:

CC# and FSR# will always be present, but the numbers after them will be different, so I would need a formula or VBA script that accounts for that. They will have the same number of digits after (6 for CC# and 7 for FSR#).

I do not need any data before or after CC# and FSR#.

I have thousands of rows, so hopefully there is a good formula or VBA.

Thanks!

 

[T. Valko]

Hello,

I need to find a way to pull text/numbers together out of a cell.

This is my text in cell M1:

000184.00@000040.99:CC#008661 FSR#5108434 PROVIDE

This is how I would like it to appear in my spreadsheet:

M1: 000184.00@000040.99:CC#008661 FSR#5108434 PROVIDE
N1: CC#008661
O1: FSR#5108434

Notes:

CC# and FSR# will always be present, but the numbers after them will be different, so I would need a formula or VBA script that accounts for that. They will have the same number of digits after (6 for CC# and 7 for FSR#).

I do not need any data before or after CC# and FSR#.

I have thousands of rows, so hopefully there is a good formula or VBA.

Thanks!

Try these:

For CC#:

=MID(M1,FIND("CC#",M1),9)

For FSR#:

=MID(M1,FIND("FSR#",M1),11)

 

[rconverse]

Formula for N1 =TRIM(MID(M1,FIND("CC#",M1,1), FIND("FSR#",M1,1)-3-FIND("CC#",M1,1)))
Formula for O1 =TRIM(MID(C30,FIND("FSR#",C30,1), FIND("PROVIDE",C30,1)-FIND("FSR#",C30,1)))

HTH,
Roger