Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trim Function
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trim Function
Yes, there's a way around it. However, it depends on the solution you need. What
do you want the results to be? -- Regards, Fred "Saxman" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trim Function
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 "Saxman" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trim Function
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" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trim Function
On 31/07/2007 23:38:06, "Fred Smith" wrote:
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trim Function
On 01/08/2007 04:50:00, JMB wrote:
=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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trim Function
On 01/08/2007 15:15:33, "Saxman" wrote:
On 01/08/2007 04:50:00, JMB wrote: =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. 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trim Function
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))),"'","") "Saxman" wrote: On 01/08/2007 15:15:33, "Saxman" wrote: On 01/08/2007 04:50:00, JMB wrote: =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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TRIM function | New Users to Excel | |||
trim function | Excel Worksheet Functions | |||
Need help with TRIM function | Excel Worksheet Functions | |||
Trim Function | Excel Worksheet Functions | |||
Trim function | Excel Worksheet Functions |