Split Text from cell

**Brawnystaff** · 05-31-2014, 10:49 PM

I have trying to split numbers from letter a cell. The numbers can appear before or after the text, and sometimes the numbers can have two hypens (for SSN), two forward slashes (/) for dates, or a decimal point and/or dollar sign. if the numbers appear first, I need them to stay in the activecell and move the letters one cell to the right. If the letters appear first in the cell, I need the numbers move one cell to the right.

I have a script below that uses Regexp, the only problem is that is only works for the hypens and I need to add in the two backslashes, decimal point, and dollar sign. I tried a few variations but to no avail. Any ideas? Thanks..

Please Login or Register  to view this content.

**Brawnystaff** · 06-01-2014, 01:24 AM

I think I have it..the only thing I cannot figure out is the "literal" dollar sign that would be part of the number. Below is the revised code. Is there a way to set it up to ignore dollar signs?

Please Login or Register  to view this content.

**Richard Buttrey** · 06-01-2014, 05:16 AM

Hi,

Are you sure you need a macro, and one requiring RegExp just to complicate it.
Post examples of your text values since there's probably a straightforward string slicing function that will be easier and quicker.

**Brawnystaff** · 06-01-2014, 11:15 AM

No, it does not have to be RegEx. Whatever works is fine.

See attached spreadsheet. Sheets labeled Before and After.

Thanks.

**AlKey** · 06-01-2014, 11:56 AM

Do you think a result of something like this would be helpful to you?

	A	B
1	JohnSmith111-55-5678	111-55-5678
2	555-12-3456Parton,Dolly	555-12-3456
3	Holmes, john$55.45	55.45
4	SmithJohn12/56/1977	12/56/1977
5	12/25/1965JohnBarton	12/25/1965
6	AneSmith654	654
7	5673smith	5673

**Brawnystaff** · 06-01-2014, 12:32 PM

No, would need text and numbers separated. Also, it letters are first, then numbers would be shifted right, if numbers are first, then letters would be shifted right.

**AlKey** · 06-01-2014, 12:55 PM

Try this UDF

Please Login or Register  to view this content.

Then use formulas
for Split 1

=IF(ISERROR(LEFT(A2)+0),NumberOut(A2),SUBSTITUTE(A2,NumberOut(A2),""))

for Split 2

=IF(ISNUMBER(LEFT(A2)+0),NumberOut(A2),SUBSTITUTE(A2,NumberOut(A2),""))

or you can use =SUBSTITUTE(A2,B2,"") (for Split 2)

	A	B	C
1	Text	Split 1	Split 2
2	JohnSmith111-55-5678	JohnSmith	111-55-5678
3	555-12-3456Parton,Dolly	555-12-3456	Parton,Dolly
4	Holmes, john$55.45	Holmes, john$	55.45
5	SmithJohn12/56/1977	SmithJohn	12/56/1977
6	12/25/1965JohnBarton	12/25/1965	JohnBarton
7	AneSmith654	AneSmith	654
8	5673smith	5673	smith

**Brawnystaff** · 06-01-2014, 01:23 PM

Thanks. It worked.

**AlKey** · 06-01-2014, 01:27 PM

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation *

Split Text from cell

LinkBack

Thread Tools

Rate This Thread

Display

Split Text from cell

Re: Split Text from cell

Re: Split Text from cell

Re: Split Text from cell

Re: Split Text from cell

Re: Split Text from cell

Re: Split Text from cell

Re: Split Text from cell

Re: Split Text from cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Split text in a cell

Macro : Split text of a single cell into multiple cell

Split text in a cell

split text from one cell

Split text to next cell down

Bookmarks

Bookmarks

Posting Permissions