Removing letters from a string

[woodyinoz]

Hi all,

I've got a column in Excel with the following data in it:

895001A
78390
783429
34224
3345G
435764F

Etc...

I need to remove the final character if it is a letter. They can be different lengths and some values may not have a letter at all but if a letter is in the string it will always be at the end....

Any help would be great....

Thanks in advance,

Woody

 

[PHV]

Have a look at the Val function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

=IF(ISNUMBER(A1),A1,IF(LEN(A1)>1,MID(A1,1,LEN(A1)-1),0))


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[AnotherHiggins]

if a letter is in the string it will always be at the end

Will there always be only one letter? If not, the formula will need to account for that.

Are there ever any single-character entries? If not, you can leave one of the conditions out of Frederico's formula.

Regardless of the answer to the last question, I would shorten Frederico's formula slightly by using LEFT instead of MID.

As you can see, there are many ways to skin that poor ol' cat. The first thing that comes to my mind is:
[COLOR=blue white]=IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-1)*1)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[fredericofonseca]

hum... you version will not work if you have an empty cell... hence my checking the lenght of the string beforehand.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[vladk]

fredericofonseca,

I would wrap your formula into =Value() to align all results as a number.

Nice formula, btw.

vladk

 

[AnotherHiggins]

hum... you[sic] version will not work if you have an empty cell

As I said, it depends on what the data looks like. Are there empty cells? Are there single-character cells? Is there always only one letter at the end of the string? The fact is that we weren't given enough info about the dataset.

And, IMHO, MID is unnecessarily complex when LEFT is all that is required.

I would wrap your formula into =Value() to align all results as a number

That's what the *1 in my formula is for. It forces all answers to be numbers.
=IF(ISNUMBER(A1),A1,[blue]LEFT(A1,LEN(A1)-1)[/blue][red]*1[/red])

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PHV]

Anyway, as we are in the VBA forum and not in the Microsoft Office one, I persist to say that the OP simply have to use the Val VBA function ...

 

[AnotherHiggins]

PH,

Good point.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Bowers74]

I can't remember where I got this function, but I have been using it for years. It should do the trick!

Function Extract_Numbers(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim lNum As String
sText = rCell
    For iCount = Len(sText) To 1 Step -1
        If IsNumeric(Mid(sText, iCount, 1)) Then
            i = i + 1
            lNum = Mid(sText, iCount, 1) & lNum
        End If
            If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
    Next iCount
Extract_Numbers = lNum + 0
End Function

Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[Bowers74]

. . . And, just in case you still wanted to look at a worksheet function:

Assuming your string is in A1 Type:

[tt]=VALUE(MID(A1,MATCH(0,(ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)*1),0),LEN(A1)-SUM((ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)*1))))[/tt]

And enter it as an Array formula by using CTRL+SHIFT+ENTER



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here