seperate text and number in the same cell

  • Thread starter Thread starter jad
  • Start date Start date
J

jad

Hi,
i have the following data :
cell a2 : aa22
cell a3 : aab80
cell a4 : acd99

how can i but just the letters in cell b2 and the number in cell c2

such as
b2 = aa
c2=22

thank you all
 
Hi

If you go here: http://www.ozgrid.com/VBA/ExtractNum.htm I have acustom
function that will extract the numbers only from the cells.

=ExtractNumber(A1)

To then get the text only use the function below in the same way as
decribed in the above link for extracting numbers

Function ExtractText(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim strText As String
sText = rCell

For iCount = Len(sText) To 1 Step -1
If Not IsNumeric(Mid(sText, iCount, 1)) Then
i = i + 1
strText = Mid(sText, iCount, 1) & strText
End If
Next iCount
ExtractText = strText
End Function

In any cell then place;

=ExtractText(A1)

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Back
Top