separating numbers from a alphanumeric cell

[Guest]

How to separate numbers from a alphanumeri cell.

For Eg: If a cell contains data as 1. Apple25orange50
The result should come as 25&50.
Eg: 2 . sdfldsj2535hlkfd2548dsfs36 - result should be as 2535&2548&36

There should be separator with "&" function.

Can any one guide me with the formula to use with "&" and without "&".

 

[Gord Dibben]

Can you live with a macro?

Public Sub StripAllAZs()
''strips out everything except numbers
'adds an ampersand between number sets
Dim myRange As Range
Dim cell As Range
Dim myStr As String
Dim i As Integer
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
For Each cell In myRange
myStr = cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _
(Asc(UCase(Mid(myStr, i, 1))) > 57) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
Selection.Replace What:=" ", Replacement:="&", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
'change Replacement:="&" to Replacement:="" if no separator wanted
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Gord, can you explain what this IF statement does:

If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _
(Asc(UCase(Mid(myStr, i, 1))) > 57) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If

Thanks,

Dave

 

[Ron Rosenfeld]

How to separate numbers from a alphanumeri cell.

For Eg: If a cell contains data as 1. Apple25orange50
The result should come as 25&50.
Eg: 2 . sdfldsj2535hlkfd2548dsfs36 - result should be as 2535&2548&36

There should be separator with "&" function.

Can any one guide me with the formula to use with "&" and without "&".

To separate out the numbers and join them with the &, you could download and
install Longre's free morefunc.xll add-in from http://xcell05.free.fr and then
use one of these formulas:


=REGEX.SUBSTITUTE(A1,"(^[A-Z]+)|([A-Z]+)","[1=,2=&]",,,FALSE)

=MCONCAT(REGEX.MID(A1,"\d+",INTVECTOR(REGEX.COUNT(A1,"\d+"),1)),"&")



What do you mean for a formula "without &"?

If you mean just the numbers with no separator, then:

=REGEX.SUBSTITUTE(A1,"\D")

will do that.


--ron