Formula to remove numbers and return text string

[Boanerges_0800]

I am looking for a formula to remove the numbers and return just the text string from the following example:

<TABLE style="WIDTH: 378pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=503 x:str><COLGROUP><COL style="WIDTH: 193pt; mso-width-source: userset; mso-width-alt: 9398" width=257><COL style="WIDTH: 185pt; mso-width-source: userset; mso-width-alt: 8996" width=246><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 193pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=17 width=257>Text and numbers example</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 185pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 width=246>Formula Result</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>110060 Sls Rev-IntracoFE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> Sls Rev-IntracoFE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>* *6020* Net Sales Internal</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>* ** Net Sales Internal</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>** 6020 NET SALES INTERNAL</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>** NET SALES INTERNAL</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>*** 6100 TOTAL NET SALES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>*** TOTAL NET SALES</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> 700060 COGPrimeIntracoFE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> COGPrimeIntracoFE</TD></TR></TBODY></TABLE>

 

[General Ledger]

I am sorry I do not have the answer but maybe I can point you in a direction to research.

It looks like you need an Array formula. It would probably incorporate the functon ISNUMBER to test each character in the source data.

Best of luck,

GL

 

[mgm05267]

Hi Boanerges,

Welcome to the board...

as per your input, if the starting numbers are fixed to 6 & assuming first 18 characters of the text is 18, you can use...

=MID(A1,7,18)

Hope this might help...

 

[daverunt]

Have a look at this post from Firefly

http://www.mrexcel.com/forum/showthread.php?t=609885

 

[Marcelo Branco]

Try this User Defined Function

Alt+F11 to open the VBEditor
Insert > Module
Then paste the code below in the right panel

Function RemoveNumbers(t As String)
    Dim i As Long, newString As String
    
    For i = 1 To Len(t)
        If Not IsNumeric(Mid(t, i, 1)) Then
            newString = newString & Mid(t, i, 1)
        End If
    Next i
    RemoveNumbers = newString
End Function

Assuming your data in A2, A3...

put this formula in B2
=RemoveNumbers(A2)

copy down

M.

 

[Boanerges_0800]

Thank you everyone. There are at least four good answers here which is better than one.

Kind regards

 

[Marcelo Branco]

Complementing my previous post

To delete leading/trailing spaces you can use in B2
=TRIM(RemoveNumbers(A2))

M.

 

[Boanerges_0800]

Hi Marco,
Do you know how I can get the macro that you wrote above to work in all spreadsheets in excel? I pasted it into my personal but it only works there. I want to use it in any sheet I open.

 

[Scott Huish]

Save it as an add-in in a blank workbook. Then load the add-in.

 

[Marcelo Branco]

Maybe (assuming the name of your personal workbook is PERSONAL.XLSB. Check)

=TRIM(PERSONAL.XLSB!RemoveNumbers(A2))

M.