Flash formula or Alpha numeric Text split

Question

Hi,

         I need to split numbers from one cell to two column,  Example: A1 cell contain Alpha numeric word like 34xxxX2.0, A2 cell have CDEFX56xxX1.0, A3 cell have 10.50GhjkX0.60,

A4 cell have Xccvz13.75X8.50. A5 have 100X250rhjjjkX3.0, A6 cell have 56X56dsfX1.6

Flash fill doing effectively. But flash sometimes not work decimal split when row have 500 more. I cannot find and not adjust fraction.

My result would be B1 cell have  34 and C1 cell have 2.0, B2 cell have 56 and C2 cell have 1.0 and B3 cell have 10.50 and C3 cell have 0.60 and B4 cell have 13.75 and C4 cell have 8.50,B5 cell have 100 and C5 cell have 3.0, B6 cell have 56 and C7 cell have 1.6

Answer 1

To:  Avinsh

re:  57 flavors of the same question

My free Custom_Functions Excel add-in contains the two worksheet functions shown in the image. ( plus another 18+ functions)

Cells are formatted to align right.

Download fromMediaFire (no ads)...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

Answer 2

Hi Nothing left to Lose,

                                          your custom Function Add -in Working Fine, but i need inbuilt of Excel.

once i saved and closed file, i cannot call your custom Function in my Existing Sheet when reopen, but i have checked in Excel add-ins. it shown appeared. Some Excel version Problem. kindly Given Code.

Answer 3

If you want to retrieve true numbers from the right of source values like CDEFX56xxX1.0, 10.50GhjkX0.60, Xccvz13.75X8.50 or 100X250rhjjjkX3.0 then the true numbers will be 1, 0.6, 8.5 and 3. It would be up to you to provide a suitable cell number format to show the decimal places.

If 1.0, 0.60, 8.50 or 3.0 are the expected return values then the you could retrieve them as text but this renders them useless in any further numerical operation.

The same goes for the left side number 10.50.

Do you want true numbers or text that look like numbers?

Option Explicit

Function GrabNumbers(str As String, bLeft As Boolean, Optional bTrue As Boolean = True)

    'Syntax:

    '=GrabNumbers(<string>, <Left side = TRUE, Right side = FALSE>, [optional TRUE for true numbers])

    Dim i As Long, j As Long, gn As String

    If Not bLeft Then str = StrReverse(str)

    For i = 1 To Len(str)

        If Val(Mid(str, i)) <> 0 Then

            For j = i To Len(str)

                Select Case Asc(Mid(str, j, 1))

                    Case 46, 48 To 57

                        gn = gn & Mid(str, j, 1)

                    Case Else

                        Exit For

                End Select

            Next j

        End If

        If gn <> vbNullString Then Exit For

    Next i

    If Not bLeft Then gn = StrReverse(gn)

    GrabNumbers = IIf(bTrue, CDbl(gn), gn)

End Function

Answer 4

A formula solution:

Enter below formula in cell B1 and copy down - 1st number in string:

=LEFT(MID(A1,MIN(INDEX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1),""),)),255),MATCH(TRUE, INDEX(ISERR(--MID( SUBSTITUTE( MID(A1, MIN( INDEX( IFERROR(FIND({1,2,3,4,5,6,7,8,9,0}, A1),""),)),255),".","0"), ROW(INDIRECT("1:"&LEN(A1))),1)),),0)-1)

Enter below formula in cell C1 and copy down - last number in string:

=MID(LEFT(A1,MATCH(1E+100,INDEX(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),))),MATCH(2,INDEX(1/ISERR(--MID( SUBSTITUTE(LEFT(A1, MATCH(1E+100, INDEX(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),))),".","0"), ROW(INDIRECT("1:"&LEN(LEFT(A1,MATCH(1E+100, INDEX(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1),)))))),1)),))+1, 255)

Regards,

Amit Tandon

www.excelanytime.com

Answer 5

Hi,

Flash Fill works just fine for me.  In cell B2:B3, type '2.0 and '1.0.  Select B2:B7 and go to Data > Flash Fill.

Hope this helps.