number from text

[narendra1302]

Dear all,

I have one column in Excel Sheet.
e.g.
LX45
rt56
roi89
wers56
KL43
k5

now how to seperate text & number - e.g. if rt56 then "rt" should be in one cell & "56" in another

Thanks
Narendra

 

[MartinL]

Hi Narendra

are your source values always letters then numbers or can you have:

ab123cd
123abc
a1b2c3
1abc2def

Martin

 

[narendra1302]

Dear MartinL,

Thanks for showing intrest.

Yes it could be
ab123cd
123abc
a1b2c3
1abc2def

Thanks
Narendra

 

[MartinL]

so are you expecting the letters to be concatenated in one cell and the numbers concatenated in another

 

[Comfy]

Give this a try.

Select the range you want to extract the Numbers and letters from.

Sub Seperate()
Dim NumStr As String, LetStr As String
Dim i As Long
Dim rng As Range

Set rng = Selection

For Each cell In rng.Cells
    For i = 1 To Len(cell.Value)
        If IsNumeric(Mid(cell.Value, i, 1)) Then
            NumStr = NumStr & Mid(cell.Value, i, 1)
        Else
            LetStr = LetStr & Mid(cell.Value, i, 1)
        End If
    Next i
Cells(cell.Row, cell.Column + 1).Value = NumStr
Cells(cell.Row, cell.Column + 2).Value = LetStr
NumStr = ""
LetStr = ""
Next
End Sub

 

[MartinL]

Comfy I concur

I was goin down ther UDF route buut if you highlight all the codes and then run the macro its work fine even with my examples.

 

[Comfy]

You could convert this into a UDF.

 

[narendra1302]

Dear Comfy,

Thanks - It works fine.

Dear MartinL,

Thanks to you also.

Thanks
Narendra

 

[MartinL]

You could convert this into a UDF.

Very true

Function Seperate(rCell As Range) As String

isnt it?

 

[narendra1302]

Dear Comfy & MartinL,

Can we have this for Date also.

That is if the number is replaced by Date, then how to get this in two different columns.

Thanks
Narendra