Truncating a string in Excel
 

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
------------------------------------------------------------------------
Sweetie's Profile: http://www.excelforum.com/member.php...o&userid=34585
View this thread: http://www.excelforum.com/showthread...hreadid=543578

Truncating a string in Excel
 

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

Truncating a string in Excel
 

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


--
Sweetie
------------------------------------------------------------------------
Sweetie's Profile: http://www.excelforum.com/member.php...o&userid=34585
View this thread: http://www.excelforum.com/showthread...hreadid=543578

Truncating a string in Excel
 

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