Extract Text From Numbers&Text&Numbers Cell

[Starrbuckk]

Hi everyone,

I have data in "A" column as follow : 00230536UE037; 86473UE023; 004729831HO0471.
What i want to do is only to extract the Text from each one of the cells. Simple +Left/+Right will not work due to the variation of the number of characteres within each cell, and positioning of said text also.

Can anyone help?

 

[Fishboy]

Hi everyone,

I have data in "A" column as follow : 00230536UE037; 86473UE023; 004729831HO0471.
What i want to do is only to extract the Text from each one of the cells. Simple +Left/+Right will not work due to the variation of the number of characteres within each cell, and positioning of said text also.

Can anyone help?

Hi Starrbuckk, welcome to the boards.

I found the following User Defined Function (UDF) over at OzGrid that should do the trick for you.

- Press ALT+F11 to open the VBA Developer window
- Find the name of your Workbook in the Project Pane in the top left hand side of the VBA Developer window
- Right-click on ThisWorkbook and select Insert->Module
- In the new window that opens simply copy and paste in the following code:

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 Not IsNumeric(Mid(stdText, i, 1)) Then
            str = str & Mid(stdText, i, 1)
        End If
    Next i
    StripNumber = str ' * 1     
End Function

- Now go back to your actual workbook and (assuming your data starts in A1) put the following in cell B1 and press enter:

=StripNumber(A1)

You can drag-fill this down the length of your column.

 

[Starrbuckk]

That did the trick, thank you, thank you thank, you. =)

 

[Starrbuckk]

- Press ALT+F11 to open the VBA Developer window
- Find the name of your Workbook in the Project Pane in the top left hand side of the VBA Developer window
- Right-click on ThisWorkbook and select Insert->Module
- In the new window that opens simply copy and paste in the following code:

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 Not IsNumeric(Mid(stdText, i, 1)) Then
            str = str & Mid(stdText, i, 1)
        End If
    Next i
    StripNumber = str ' * 1     
End Function

How would i go about saving the Custom Function for Future use?

 

[Fishboy]

Glad to hear it did the trick.

With regards to saving it for future use I assume you mean in workbooks other than the current one?

- From the VBA Developer window, double-click on Module1.
- Just below the Project Pane on the left you should see the Properties pane.
- Find where it says (Name) Module1, click in the box and change the name from Module1 to something you will easily remember and press enter.
- Go back up the Project Pane, right-click on whatever you have renamed Module1 as and select Export File.
- A "Save As" dialogue box will come up allowing you to save the module out to a place of your choosing (make sure it is somewhere you will be able to find it again afterwards)
- In any new workbooks, go to the VBA Developer window, right-click on ThisWorkbook and select Import
- Browse to where you exported the module and select it to add it to your new workbook.

 

[AlanY]

an alternative

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")

 

[Starrbuckk]

Is there a way to save it in "Personal" xlsb so i wont have to load everytime i have to use it?

 

[Starrbuckk]

an alternative

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")

Thank you, it works aswell =)

 

[Fishboy]

Is there a way to save it in "Personal" xlsb so i wont have to load everytime i have to use it?

Looks like a post on StackOverflow here describes the best way of doing it.