Extracting only text from a string containing text and numbers

[PGInsight]

Hi can anybody give me a formula that will extract only the text from these examples

DEBDEN - 12 EAST ARNDALE - 150 LITTLEHAMPTON - 2999 <tbody> </tbody><colgroup><col></colgroup> <tbody> </tbody><colgroup><col></colgroup>

<tbody>
</tbody><colgroup><col></colgroup>

 

[Maagaard]

Hi,

go to the Excel Visual Basic editor (ALT+F11) and insert this in a module:

Function OnlyText(Ran As String) As StringDim StringCount As Integer
StringCount = 1
Do Until Mid(Ran, StringCount, 1) = "-"
    StringCount = StringCount + 1
Loop
Do Until Mid(Ran, StringCount + 1, 1) = " " And Mid(Ran, StringCount, 1) <> "-"
    StringCount = StringCount - 1
Loop


OnlyText = Left(Ran, StringCount)


End Function

You can insert a module by selecting Insert-->Module in the menu bar.

you can now use the formula: =OnlyText(A1) in your spreadsheet.

Hope this helps!

Maagaard

 

[Snakehips]

PGInsight,
WElcome to MrExcel.

If your examples are tyical in that the - separates the text from the numbers then you can use....

=TRIM(LEFT(A1,FIND("-",A1)-1))

Hope that helps.

 

[PGInsight]

hi thx for this, I'm getting a compile error when I run this 'Expected:end of statement'

 

[PGInsight]

great, got it-thanks that works perfectly

 

[Maagaard]

hi thx for this, I'm getting a compile error when I run this 'Expected:end of statement'

For some reason, its pasted in to this page wrong change the top line: Function OnlyText(Ran As String) As StringDim StringCount As Integer
to:
Function OnlyText(Ran As String) As String
Dim StringCount As Integer

Be aware that this only works, as long as there is a - between text and numbers.

 

[Rick Rothstein]

go to the Excel Visual Basic editor (ALT+F11) and insert this in a module:

Function OnlyText(Ran As String) As StringDim StringCount As Integer
StringCount = 1
Do Until Mid(Ran, StringCount, 1) = "-"
    StringCount = StringCount + 1
Loop
Do Until Mid(Ran, StringCount + 1, 1) = " " And Mid(Ran, StringCount, 1) <> "-"
    StringCount = StringCount - 1
Loop

 OnlyText = Left(Ran, StringCount)
 
End Function

You can insert a module by selecting Insert-->Module in the menu bar.

you can now use the formula: =OnlyText(A1) in your spreadsheet.

Snakehips has posted a direct formula that the OP can use and that would be better for him to use than a UDF like your OnlyText; however, I thought you might be interested in seeing a UDF which does what yours does but using only one line of code...

Function OnlyText(Ran As String) As String
  OnlyText = Trim(Split(Ran, "-")(0))
End Function

 

[István Hirsch]

This formula also works for the sample provided:

=REPLACE(A1,FIND(" -",A1),99,"")

 

[Rick Rothstein]

Be aware that this only works, as long as there is a - between text and numbers.

Here is my one-liner modified to handle that problem (I think you could have employed the same trick in your UDF to avoid the problem as well)...

Function OnlyText(Ran As String) As String
  OnlyText = Trim(Split(Ran & "-", "-")(0))
End Function