Seperate letters and numbers in a cell
 

I have a spreadsheet where one column has cells with letters and numbers. I
want to seperate the letters into one cell and the numbers into another cell.
For example:

Cell Prefix Suffix
abcd041 abcd 041
qjwxkl678 qjwxkl 678
abc1455 abc 1455

Thanks for any help on this.

Seperate letters and numbers in a cell
 

Here is an example using columns A, B, & C:

Sub split_um()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
Cells(i, "B").Value = ""
Cells(i, "C").Value = ""
v = Cells(i, "A").Value
For j = 1 To Len(v)
ch = Mid(v, j, 1)
If IsNumeric(ch) Then
Cells(i, "C").Value = Cells(i, "C").Value & ch
Else
Cells(i, "B").Value = Cells(i, "B").Value & ch
End If
Next
Next
End Sub

--
Gary''s Student - gsnu200768


"Keith S" wrote:

I have a spreadsheet where one column has cells with letters and numbers. I
want to seperate the letters into one cell and the numbers into another cell.
For example:

Cell Prefix Suffix
abcd041 abcd 041
qjwxkl678 qjwxkl 678
abc1455 abc 1455

Thanks for any help on this.

Seperate letters and numbers in a cell
 

Assuming your data starts in Row 2 (where Row 1 is assumed to be a header
row), and assuming your data is in Column A, put this formula in your Prefix
column...

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"012345 6789"))-1)

and put this formula in your Suffix column...

=RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1 )

and copy down as needed.

Rick


"Keith S" wrote in message
...
I have a spreadsheet where one column has cells with letters and numbers.
I
want to seperate the letters into one cell and the numbers into another
cell.
For example:

Cell Prefix Suffix
abcd041 abcd 041
qjwxkl678 qjwxkl 678
abc1455 abc 1455

Thanks for any help on this.

Seperate letters and numbers in a cell
 

Put this in D1:
=MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Put this in B1:
=LEFT(A1,D1-1)

Put this in C1:
=MID(A1,D1,255)

Select B1:D1
and drag down your range.

Select B:D
Edit|copy
Edit|Paste special|values

Delete column D.

Keith S wrote:

I have a spreadsheet where one column has cells with letters and numbers. I
want to seperate the letters into one cell and the numbers into another cell.
For example:

Cell Prefix Suffix
abcd041 abcd 041
qjwxkl678 qjwxkl 678
abc1455 abc 1455

Thanks for any help on this.

--

Dave Peterson

Seperate letters and numbers in a cell
 

I guess we should wrap those formulas in an IF function call to properly
handle the case when the data cell is empty...

Prefix Cell
============
=IF(A2="","",LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9 },A2&"0123456789"))-1))

Suffix Cell
============
=IF(A2="","",RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1 ))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Assuming your data starts in Row 2 (where Row 1 is assumed to be a header
row), and assuming your data is in Column A, put this formula in your
Prefix column...

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"012345 6789"))-1)

and put this formula in your Suffix column...

=RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1 )

and copy down as needed.

Rick


"Keith S" wrote in message
...
I have a spreadsheet where one column has cells with letters and numbers.
I
want to seperate the letters into one cell and the numbers into another
cell.
For example:

Cell Prefix Suffix
abcd041 abcd 041
qjwxkl678 qjwxkl 678
abc1455 abc 1455

Thanks for any help on this.

Seperate letters and numbers in a cell
 

Thanks everyone. The response from Rick worked the easiest. I appreciate
everyone's help.

"Dave Peterson" wrote:

Put this in D1:
=MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Put this in B1:
=LEFT(A1,D1-1)

Put this in C1:
=MID(A1,D1,255)

Select B1:D1
and drag down your range.

Select B:D
Edit|copy
Edit|Paste special|values

Delete column D.

Keith S wrote:

I have a spreadsheet where one column has cells with letters and numbers. I
want to seperate the letters into one cell and the numbers into another cell.
For example:

Cell Prefix Suffix
abcd041 abcd 041
qjwxkl678 qjwxkl 678
abc1455 abc 1455

Thanks for any help on this.

--

Dave Peterson

Seperate letters and numbers in a cell
 

Hello,

You already got some suggestions.

I find regular expressions quite helpful:
Enter into B1
=RegExpReplace(A1,"(\D*)(\d*).*","$1")
And into C1
=RegExpReplace(A1,"(\D*)(\d*).*","$2")
for example.

The UDF you can find he
http://www.sulprobil.com/html/regexp.html

Regards,
Bernd