Hi I have some data in excel sheet in mix (Letter & Number) format e.g

A
SCCL 1574
LC08199
KS03559
SC70341
SCCL 2033
LC08758
AB04291
KS04018


I want this field to split into 2 (Letter should be in separate and number separate). e.g
A   B
SCCL   1574
LC   08199
KS   03559
SC   70341
SCCL   2033

I don’t know how to do that.

Could anyone have an idea, Thanks in advance?

Don't know how to do it in Excel but the attached will do it in Access. If no Excel answer forthcoming then paste your Excel colum into Access table as shown and then send result back to Excel. I put your data in.

Open the query. Edit:Forgot to add, the query is using the function in the module

Using a function like this

=MIN(IF(ISERROR(FIND("1",A1,1)),999,FIND("1",A1,1)),
IF(ISERROR(FIND("2",A1,1)),999,FIND("2",A1,1)),
IF(ISERROR(FIND("3",A1,1)),999,FIND("3",A1,1)),
IF(ISERROR(FIND("4",A1,1)),999,FIND("4",A1,1)),
IF(ISERROR(FIND("5",A1,1)),999,FIND("5",A1,1)),
IF(ISERROR(FIND("6",A1,1)),999,FIND("6",A1,1)),
IF(ISERROR(FIND("7",A1,1)),999,FIND("7",A1,1)),
IF(ISERROR(FIND("8",A1,1)),999,FIND("8",A1,1)),
IF(ISERROR(FIND("9",A1,1)),999,FIND("9",A1,1)),
IF(ISERROR(FIND("0",A1,1)),999,FIND("0",A1,1)))

you can atleast determain the first position of any number.
Then use Left() and Mid() to fetch the seperate strings.

I am almost sure there is a better way but cannot find it for the moment.
i.e. Value doesnt seem to work.

Best way is to probably parse each character in the string and use instr and isnumeric functions to determine when the first numeric character occurs and then use the left and right functions to split the string.

If you need this a lot then you could create a couple of custom functions...

Function AlphaString(strInput As String) As String
'Returns a string containing only the alpha characters of the input string
Dim i As Integer
Dim intAsc As Integer
AlphaString = ""
i = 1
While i <= Len(strInput)
intAsc = Asc(Mid(strInput, i, 1))
If (intAsc >= 65 And intAsc <= 90) Or (intAsc >= 97 And intAsc <= 122) Then
'character is Alpha
AlphaString = AlphaString + Mid(strInput, i, 1)
End If
i = i + 1
Wend
End Function


Function NumericString(strInput As String) As String
'Returns a string containing only the numeric characters of the input string
Dim i As Integer
Dim intAsc As Integer
NumericString = ""
i = 1
While i <= Len(strInput)
intAsc = Asc(Mid(strInput, i, 1))
If (intAsc >= 49 And intAsc <= 57) Then
'character is Numeric
NumericString = NumericString + Mid(strInput, i, 1)
End If
i = i + 1
Wend
End Function

Then you can use these functions like any other

=AlphaString("ABH234D") will return "234"
and
=NumericString("ABH234D") will return "ABHD"

hth
Chris

Thank you every one for help. i have learn many things.

thanks again.