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

 

[Comfy]

Was thinking possibly like this, but as with anything in Excel/VBA there are several different ways to di it

Function GetNum(Extract As String) As String
Dim i As Long
Dim NumStr As String
NumStr = ""
    For i = 1 To Len(Extract)
        If IsNumeric(Mid(Extract, i, 1)) Then NumStr = NumStr & Mid(Extract, i, 1)
    Next i
GetNum = NumStr
End Function
Function GetLet(Extract As String) As String
Dim i As Long
Dim LetStr As String
LetStr = ""
    For i = 1 To Len(Extract)
        If Not IsNumeric(Mid(Extract, i, 1)) Then LetStr = LetStr & Mid(Extract, i, 1)
    Next i
GetLet = LetStr
End Function

To use type either, =getlet(cell reference) or =getnum(cell reference)

e.g. =getlet(A1) would return just the letters in the cell you type the formula

 

[Comfy]

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

Could you post a before and after example, I don't quite understand the question.. Sorry.

 

[narendra1302]

Dear Comfy,

Thanks for the functions - It works perfectly.

Now my another question is - If we replace number by Date, then how to seperate it.

Now when I use the funtion getnum() for the text with date, it shows

e.g. rtsd02/03/09asdf
=getnum() shows 020309
& =getlet() shows rtsd//asdf.

Thanks
Narendra

 

[narendra1302]

Dear All,

Any update?

Thanks
Narendra

 

[Comfy]

Sorry, but this is the best that I could come up with:

Function GetDate(Extract As String, dFormat As String) As String
Dim i As Long, j As Long, oStart As Long
Dim DateStr As String

oStart = 1
DateStr = ""
    For i = 1 To Len(dFormat)
        If Mid(dFormat, i, 1) = "/" Then
            DateStr = DateStr & Mid(Extract, InStr(oStart, Extract, "/") - j, j) & "/"
            oStart = InStr(oStart, Extract, "/") + 1
            j = 0
        Else
            j = j + 1
        End If
    Next i
    DateStr = DateStr & Mid(Extract, oStart, j)
GetDate = DateStr
End Function

It's not ideal and hopefully someone else will be able to suggest a better solution as this will not work under several circumstances.

E.G.

If the string contains "/" prior or after the date "vwrv/ervr10/09/2052wecec/wec" this will not work.

This will only work if the date separator is "/".

If the string in A1 was abcde01/01/2011feg9847

and you specified =GetDate(A1,"dd/mm/yyyy") it would return 01/01/2011
but if you specified =GetDate(A1,"dd/mm/yy") it would return 01/01/20

there is no checking for valid dates..... Sorry

 

[narendra1302]

Dear Comfy,

Thanks for the Code.

It works perfectly as you told. And solves my purpose.

It will be added advantage if somebody else to see & correctly further for any date format.

Thanks & Regards
Narendra