Hi all!
Suppose I have a cell with for example ABC 123 in it. How would I move the 123 in to the adjacent (right) cell?
Robert
We will be implementing some important changes during 25th and 26th May 2024 which may result in an outage period of the website. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
Hi all!
Suppose I have a cell with for example ABC 123 in it. How would I move the 123 in to the adjacent (right) cell?
Robert
Re: Seperating Text from numbers
Data > Text to columns with a space as separator.
Or using the MID and the FIND function together. Find for the space and use the mid function to lift up the characters coming after the space.
=MID(A1,FIND(" ",A1)+1,100)
100 is just a large number to cover all characters coming after the space.
Or you the LEN function: number of characters in cell.
=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))
Wigi
Re: Seperating Text from numbers
Thank you so much
Robert
Re: Seperating Text from numbers
Quote from WigiDisplay MoreData > Text to columns with a space as separator.
Or using the MID and the FIND function together. Find for the space and use the mid function to lift up the characters coming after the space.
=MID(A1,FIND(" ",A1)+1,100)
100 is just a large number to cover all characters coming after the space.
Or you the LEN function: number of characters in cell.
=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))
Wigi
I think I spoke to soon, this works if there is a space between the letters and the numbers. What if there is no space? How could I seperate them then?
Robert
Re: Seperating Text from numbers
Robert,
Sub split()
Dim i As Integer
Dim letnums As String
letnums = ActiveCell.value
For i = 1 To Len(letnums)
Select Case Mid(letnums, i, 1)
Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
ActiveCell.value = Mid(letnums, 1, i - 1)
ActiveCell.Offset(0, 1).value = Mid(letnums, i)
Exit Sub
Case Else
End Select
Next i
End Sub
Display More
in a standard module.
Wigi
Re: Seperating Text from numbers
Wigi's will work for a value like abc123, but will not work for 123abc..... you could try this UDF
Function StripNumber(stdText As String)
Dim str As String, i As Integer
'strips the number from a longer text string
stdText = Trim(stdText)
For i = 1 To Len(stdText)
If IsNumeric(Mid(stdText, i, 1)) Then
str = str & Mid(stdText, i, 1)
End If
Next i
StripNumber = str * 1
End Function
Display More
Use it like a normal function, i.e.
=StripNumber(A1)
Re: Seperating Text from numbers
This looks all too familiar
http://www.ozgrid.com/forum/showthread.php?t=47962
Re: Seperating Text from numbers
either that or there are two hgus393's :yikes:
Re: Seperating Text from numbers
No there is just one hgus393, that is me, myself and I. Yeah it look very similar does it not.. I should have pasted the link to the previous post ..sorry!
Re: Seperating Text from numbers
And thank you very much for the functions
Robert
Don’t have an account yet? Register yourself now and be a part of our community!