Separating Alpha and Numeric

[maheshmaxi]

Hi

I have data in column with combination of alpha-numeric like "Member # 308166 RCI Number 4801-60436". can I split this without using copy and paste?

Pls help me regarding...

 

[Stormseed]

How do you want to split it ? In other words, what part of the text string ?

 

[maheshmaxi]

"A" column contains "Member # 308166 RCI Number 4801-60436". I wanna split this in "B" and "C"as "Member # RCI Number" and "308166 4801-60436". but our prior most motive is to seperate the numericals.

 

[Stormseed]

Select the whole column, Press Ctrl + H --> In the "Find what" box, type: "RCI Number" (without quotes). Leave the "Replace with" box empty and click on "Replace All" and finally click on Close.

 

[Richard Schollar]

Hi

A formula based approach could be:

assuming text values in column A then in B to get the member number:

=TRIM(MID(A1,FIND("#",A1)+1,FIND("RCI",A1)-FIND("#",A1)-1))

and col C to get the RCI number:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))

copy formulas down as required.

 

[maheshmaxi]

But the problem is I dont have unique data, "A" column contains data in zigzag like below:
"CM Member # 42628 RCI Member # 480128309"
"CM Mem # 48475 name: Kapadia Jay"
"member # A20322010254 Name: S.S., Nagarsenker"
"Guest of R K Patnia M# 2001a3010216"

I need to extract only numeric value

 

[maheshmaxi]

better if there is any direct method to extract only numeric value with using formula or macro... pls let me know if there is something...

 

[Richard Schollar]

The following VBA function will extract all the numeric digits within the referenced cell. Separate strings of numeric digits will be delimited by commas:

Function GetNumbers(s As String)
Dim temp As String, i As Long, oMatches
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "\d+"
    If .test(s) Then
        Set oMatches = .Execute(s)
        For i = 0 To oMatches.Count - 1
            temp = temp & oMatches(i) & ","
        Next i
        GetNumbers = Left(temp, Len(temp) - 1)
    End If
End With
End Function

Stick in a standard module then use in the sheet like:

=GetNumbers(A1)

 

[maheshmaxi]

Thanks Richard,

I am using this and will revert you shortly...

 

[maheshmaxi]

Richard it's working fine... Thanks a lot...