Truncating a string in Excel

[Sweetie]

Let's say that I do have list of analytes with names 1,2,3-Trichloromethane.
I would like to short this list, I don't want the integers on the front, just the string part of it.
Can I use conditional formula to short then in a alphabetical way.
If yes, please do share it with me

thank you
Sweetie

[LenB]

I don't know if it can be done easily with formulas. It can be done
with this macro. It assumes your list starts in A1 and goes down. The
results will be in column B. Post back if you need help creating a macro.

Len


Sub RemoveLeadingNumbers()
'Sub to remove leading non alphabetic characters from a column.
'example: 1,2,3-Trichloromethane becomes Trichloromethane

'The result will be put in the cell to the right.
'Make sure the next column is empty!
'It will stop at the first blank cell below so don't leave any empty rows.

Dim intI As Integer
Dim strRawValue As String

Range("A1").Activate 'change A1 to where the top of the list is.

Do While Len(Trim(ActiveCell.Value)) > 0
strRawValue = ActiveCell.Value
intI = 1
'Add whatever characters you want to keep to the alphabet string below
Do While InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ", _
UCase(Mid(strRawValue, intI, 1))) = 0 _
And intI <= Len(strRawValue)
intI = intI + 1
Loop
If intI < Len(strRawValue) Then
'a character is found, so save the result.
ActiveCell.Offset(0, 1).Value = _
Right(strRawValue, Len(strRawValue) - intI + 1)
End If
ActiveCell.Offset(1, 0).Activate
Loop

End Sub



Sweetie wrote:
> Let's say that I do have list of analytes with names
> 1,2,3-Trichloromethane.
> I would like to short this list, I don't want the integers on the
> front, just the string part of it.
> Can I use conditional formula to short then in a alphabetical way.
> If yes, please do share it with me
>
> thank you
> Sweetie
>
>

[Sweetie]

Thank you LenB for your prompt reply and for sharing ideas

I don't have any experience writing a macro, so I wanted to stay away from it.
I had the same thing in my mind too(zero knowledge in Macro), I thought that there would be a way to get around in conditional functioning.


thank you
Sweetie

[paul.robinson@it-tallaght.ie]

Hi
If you always have a "-" character before the text bit and your text is
in A2 then try

=Trim(RIGHT(A2,LEN(A2)-FIND("-",A2)))

This finds the postion of the "-", subtracts it from the length of the
text (so that is the length of the word you want), then extracts that
much text from the right hand side of the whole string. The trim deals
with any blank space lying about.
regards
Paul