Please help to split in excel to extract numeric digits 2

[jancypaul]

Dear Expertise,

Please help me to split the cell having alphanumeric digits in Excel.

For example, I have the following value in the column (A1..A10)


12675ABFGJJ
2352GHTDGJ
34897BBFGT
56734YHTGK
3234444XCV



from the above value , I have to split as follows


12675 ABFGJJ
2352 GHTDGJ
34897 BBFGT
56734 YHTGK
3234444 XCV

 

[mintjulep]

Don't think it can be done with native functions. You'll need a UDF. This one is crude, but works for your example.

Public Function Breakup(Mashed As String, Out As Boolean) As Variant
trimMashed = Trim(Mashed)
For I = 1 To Len(trimMashed)
    character = Mid(trimMashed, I, 1)
    If Asc(character) < 65 Then
        N = N & character
    Else
    t = t & character
    End If

Next I

If Out = True Then Breakup = CLng(N)
If Out = False Then Breakup = CStr(t)
    
End Function

 

[SkipVought]

or...
pcode]
Public Function Breakup(Mashed As String, Out As Boolean) As Variant
For I = 1 To Len(Trim(Mashed))
character = Mid(trimMashed, I, 1)
select case character
case "0" to "9"
if out then Breakup = Breakup & character
Case else
if not out then Breakup = Breakup & character
end select

Next I
End Function
[/code]


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[mintjulep]

That would return a string of numeric characters for the number portion.

I like the recursive call though.

 

[yogia]

Hi jancypaul:

Following is a formula based approach ...

if your comboString is in column A, and you want to parse the numeric portion in column B and the remaining in column C,

then formula in cell B1 ...
=LEFT(A1,LEN(LOOKUP(9.9E+307,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))))+0)))

and formula in cell C1 ...
=MID(A1,LEN(LOOKUP(9.9E+307,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))))+0))+1,255)

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[strongm]

Following the format of the functions already provided (and assuming that your data is always numeric followed by alpha):

[blue]
Public Function SplitIt(strSource As Range, Out As Boolean) As Variant
    SplitIt = Val(strSource)
    If Not Out Then SplitIt = Right(strSource, Len(strSource) - Len(SplitIt))
End Function[/blue]

 

[mintjulep]

yogia, your formulas make my head hurt

strongm, no fair using built-in functions