Seperate letters and numbers in a cell

[Keith S]

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.

 

[Gary''s Student]

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

 

[Rick Rothstein \(MVP - VB\)]

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&"0123456789"))-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

 

[Dave Peterson]

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 B11
and drag down your range.

Select B
Edit|copy
Edit|Paste special|values

Delete column D.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Keith S]

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

 

[Bernd P]

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 here:
http://www.sulprobil.com/html/regexp.html

Regards,
Bernd