How to remove extra characters from a data set when transferring data via text to column

[thekodak]

Here's the situation. we are moving to a new CRM (or to a single REAL CRM if you want to be honest) and we need each phone number in a new column with out the jargon (random notes) behind it.

The phone number are in one column now, separated by an alt return. The phone number look like the sample below, with the phone number in the front of each row with varying jargon in back depending on who was maintaining it over the last 15 years. Each cell of phone numbers could have up to 20 numbers in it.
To upload into the new CRM, I need each phone number in its own separate column with no jargon.

I can use "text to Columns" to delimit using the ALT return (0010), but that brings over the jargon. I was going use some code in the transferred cells to clean up the phone numbers but when I use text to columns it removes any of the formulas in those cells.

We have thousands of data points. I need some help. If the staff has to clean up by hand they will kill me.
I'm using office 365 business and windows 10.

Here's a sampling of the data.

(123) 456-7890 (ML) (M) (99%) -bob
(231) 456-7980 (PL) (L) (88%)
(123) 456-9870 (TH) (F)(72%)
(123) 456-0987 (KO) -fax

 

[oldbrewer]

are those 4 numbers in a single cell ?

 

[jtakw]

Hi,

If your Phone Numbers are Always in this format (###) ###-####

Would this work for you?

Book1
	A	B	C	D	E
1	(123) 456-7890 (ML) (M) (99%) -bob (231) 456-7980 (PL) (L) (88%) (123) 456-9870 (TH) (F)(72%) (123) 456-0987 (KO) -fax	(123) 456-7890	(231) 456-7980	(123) 456-9870	(123) 456-0987
Sheet101

Cell Formulas
Range	Formula
B1	=LEFT(TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",500)),COLUMNS($B1:B1)*500-499,500)),14)

B1 formula copied across as far as needed.

 

[oldbrewer]

after your replace alt 0010 with a single space character you might have 100 bob 200 fred 300 harry

in a single cell

use a macro to find non numeric characters and delete them leaving you with 100 200 300

the find a double space and replace with a single space

 

[thekodak]

are those 4 numbers in a single cell ?

yes. but the number of number in a cell can vary.

 

[jtakw]

You clicked "Thanks" for my post #3 , but I don't know if it's working for you.
I've re-read your OP, since there may be up to 20 Phone Numbers in the cell, choose one of these updated formulas, they'll accommodate Longer strings.

Book1
	A	B	C	D	E
1	(123) 456-7890 (ML) (M) (99%) -bob (231) 456-7980 (PL) (L) (88%) (123) 456-9870 (TH) (F)(72%) (123) 456-0987 (KO) -fax	(123) 456-7890	(231) 456-7980	(123) 456-9870	(123) 456-0987
2	(123) 456-7890 (ML) (M) (99%) -bob (231) 456-7980 (PL) (L) (88%) (123) 456-9870 (TH) (F)(72%) (123) 456-0987 (KO) -fax	(123) 456-7890	(231) 456-7980	(123) 456-9870	(123) 456-0987
Sheet101

Cell Formulas
Range	Formula
B1	=LEFT(TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",1000)),COLUMNS($B1:B1)*1000-999,1000)),14)
B2	=LEFT(TRIM(MID(SUBSTITUTE($A2,CHAR(10),REPT(" ",LEN($A2))),COLUMNS($B2:B2)*LEN($A2)-LEN($A2)+1,LEN($A2))),14)

Same as before, formulas copied across as far as needed.
B2 formula is a bit longer, but is not limited by the length of the Text string in the cell.

 

[thekodak]

OM GOD that worked! thank you so much! the whole staff here loves you!

 

[thekodak]

thanks! so much! this was a great help!

 

[jtakw]

You're very welcome, welcome to the forum.