Trim Function

[Saxman]

I posted recently under the 'Parsing' heading.

The following function works fine except when it encounters certain data.

=UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)))

This converts:-

Maggie May 33 to MAGGIE MAY
Have Your Own Way 691 31J) to HAVE YOUR OWN WAY,
but My Immortal (ex6) 32 gets converted to,
MY IMMORTAL (EX.

Altering the value at the end of the function to -4 gets rid of '(EX', but
also trims Maggie May 33 to MAGGIE M, which defeats the object for the
VLOOKUP function, i.e, you gain some and lose some.

Any way around this?

 

[Fred Smith]

Yes, there's a way around it. However, it depends on the solution you need. What
do you want the results to be?

 

[JLatham]

First, PLEASE give Fred Smith more info! I'd love to see it as a worksheet
function.

In the meantime, here's a user defined function (UDF) that I think will do
the job.
Maggie May 33 --> MAGGIE MAY
Have Your Own Way 691 31J) --> HAVE YOUR OWN WAY
My Immortal (ex6) 32 --> MY IMMORTAL
no leading or trailing spaces.

To put the code to use, [Alt]+[F11] to open the VB editor, if no module
available, choose Insert | Module from the VB editor menu and copy and paste
the code below into the module. Close the VB Editor.

To use it, in any formula type =trimatnum(A2)
where A2 is a cell with the text to be trimmed down.

Public Function TrimAtNum(Target As Range) As String
Const numZero = "0"
Const numNine = "9"
Dim LC As Integer ' (forward) Loop Counter
Dim RC As Integer ' (reverse) Loop Counter

If IsEmpty(Target) Then
Exit Function
End If
TrimAtNum = UCase(Trim(Target.Value)) ' default
If Left(TrimAtNum, 1) >= numZero And _
Left(TrimAtNum, 1) <= numNine Then
Exit Function ' return entire string, 1st char is numeric
End If
For LC = 1 To Len(TrimAtNum)
If Mid(TrimAtNum, LC, 1) >= numZero And _
Mid(TrimAtNum, LC, 1) <= numNine Then
'keep the number for now!
TrimAtNum = Left(TrimAtNum, LC)
Exit For
End If
Next
If Right(TrimAtNum, 1) >= numZero And _
Right(TrimAtNum, 1) <= numNine Then
For RC = LC To 1 Step -1
If Mid(TrimAtNum, RC, 1) = " " Then
TrimAtNum = Left(TrimAtNum, RC - 1)
Exit Function
End If
Next
End If
End Function

 

[JMB]

I s'pose you could try:
=UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"("},A2&"0123456789("))-1)))

which will parse the string at either the first number or first "(" it
finds. Just be sure the rest of your data does not have something in
parentheses "(" that you want to be included in the results. Otherwise, more
details would be needed concerning exactly what conditions would have to be
met to chop off the text inside the parentheses.

 

[Saxman]

Yes, there's a way around it. However, it depends on the solution you
need. What do you want the results to be?

I just want the name left intact. If the name consists of multiple words, I
would like those left intact.

Ater the names could be the following:-

Any number from 1 to 1000.
Any number from 1 to 1000 + (13F). (The number in brackets could be 1 to 1000
and include J instead of F. Any of the above could be preceded by:-
(ex3)
(ex4)
(ex5)
(ex6)
(ex7)

Thanks for the feedback everybody.

I'll give the the other suggestions a whirl later on.

 

[Saxman]

=UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"("},A2&"0123456789("))-1)))

This appears to work fine ATM.

I'm very grateful to you and all.

 

[Saxman]

This appears to work fine ATM.

I'm very grateful to you and all.

Just had a thought. Never asked because I never thought it was possible. To
the above function could one remove any apostrophes ( ' ) anywhere in the
actual name?

i.e.

Dewar's Gold = DEWARS GOLD
Bob's Your Uncle = BOBS YOUR UNCLE
King's Diamond = KINGS DIAMOND

 

[JMB]

you could use substitute. search the text string for ' and replace with ""
(empty string).

The general syntax would be:
=Substitute(TextString, "'","")

Using your specific formula in the place of TextString:
=SUBSTITUTE(UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"("},A2&"0123456789("))-1))),"'","")