trim first seven charactors from a string

Eddie G. · Feb 28th, 2002, 08:53 AM

i have a list like this:

(01101) LOS ANGELES
(01102) SAN DIEGO
(01103) MODESTO

i want a formula that will trim the numbers in paren. so the list will look like:

LOS ANGELES
SAN DIEGO
MODESTO

Mudface · Feb 28th, 2002, 09:00 AM

Use =MID(A1,9,LEN(A1)), where your text is in cell A1. This will remove the first 7 chrs and the space.

Aladin Akyurek · Feb 28th, 2002, 09:21 AM

Eddie,

If your data is that regular, you can also use Data|Text to Columns, using the Fixed Width option.

Aladin

Chris Davison · Feb 28th, 2002, 12:05 PM

Quote:

=MID(A1,9,LEN(A1))

Evening Mudface,

that's interesting, you ended with just LEN(A1) rather than the difference between the initial text length minus the discarded text

If you read this Nate, would this make your filename [without path] formula a little easier ?

Cheers both
Chris

Feb 28th, 2002, 12:08 PM

CHRIS,

MUDFACES'S FORMULA WORKED WELL, HOW WOULD YOU HAVE WRITTEN IT? I AM CURIOUS....THANKS ALOT...EDDIE G.

IML · Feb 28th, 2002, 12:23 PM

Perhaps something like,

=RIGHT(A1,(LEN(A1)-7)*(LEN(A1)>7))

Mudface · Feb 28th, 2002, 12:25 PM

Hi, Chris, how's it going

.

In reply to Anon, I think what Chris is saying is that, normally it would have been written as

=MID(A1,9,LEN(A1)-9)

MID just returns all the text from the ninth character onwards.

Edit: Sorry, talking out me bum there, been a long day

[ This Message was edited by: Mudface on 2002-02-28 11:38 ]

Chris Davison · Feb 28th, 2002, 01:24 PM

gawwwd

Eddie, thanks - yeah, I like miudafce's method : I hadn't realised you could end with LEN(x) where it *exceeded* the last arguement in a mid function : I would have assumed it returned an error or a load of blank characters, but it doesn't. So much kudos to Mudface.

There was a similar posting from Nate about trimming down an =cell("filename",a1) request so that it only contained the filename rather than the whole path. More successfully than I, Nate managed this impressively by (ummmm) locating the "[" character in the full path, and putting it's position into a mid function, with the last part of the MID function counting for n cahracters where n equaled the full length of the cell filename function LESS the number returned from the earlier FIND function (ie counting exactly for the remainder of characters)

*takes a break*

My observation (not a criticism or anything) was that maybe that that last bit to determine the remaining characters to count for in the last bit of the MID function might not be neccessary based on Mudface's method per the above

I am relatively new to the FIND function, so Nate's formula was fab. Ditto Mudface's. It just occured to me they could be "combined" to save a bit of typing.

Hope this makes sense to anyone who's still reading !

Chris
:-Q

[ This Message was edited by: Chris Davison on 2002-02-28 12:25 ]

Eddie, here's Nate's thread to which I was reffering.... hope it helps satisfy your curiosity more than I was able to !!

http://www.mrexcel.com/board/viewtop...ic=864&forum=2

[ This Message was edited by: Chris Davison on 2002-02-28 12:32 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 28th, 2002, 08:53 AM	#1
Eddie G. Board Regular Join Date: Feb 2002 Location: OKC Posts: 98	i have a list like this: (01101) LOS ANGELES (01102) SAN DIEGO (01103) MODESTO i want a formula that will trim the numbers in paren. so the list will look like: LOS ANGELES SAN DIEGO MODESTO

Feb 28th, 2002, 09:00 AM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Use =MID(A1,9,LEN(A1)), where your text is in cell A1. This will remove the first 7 chrs and the space.

Feb 28th, 2002, 09:21 AM	#3
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Eddie, If your data is that regular, you can also use Data\|Text to Columns, using the Fixed Width option. Aladin

Feb 28th, 2002, 12:08 PM	#5
Guest Posts: n/a	CHRIS, MUDFACES'S FORMULA WORKED WELL, HOW WOULD YOU HAVE WRITTEN IT? I AM CURIOUS....THANKS ALOT...EDDIE G.

Feb 28th, 2002, 12:23 PM	#6
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	Perhaps something like, =RIGHT(A1,(LEN(A1)-7)*(LEN(A1)>7))

Feb 28th, 2002, 12:25 PM	#7
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Hi, Chris, how's it going . In reply to Anon, I think what Chris is saying is that, normally it would have been written as =MID(A1,9,LEN(A1)-9) MID just returns all the text from the ninth character onwards. Edit: Sorry, talking out me bum there, been a long day [ This Message was edited by: Mudface on 2002-02-28 11:38 ]

Feb 28th, 2002, 01:24 PM	#8
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	gawwwd Eddie, thanks - yeah, I like miudafce's method : I hadn't realised you could end with LEN(x) where it exceeded the last arguement in a mid function : I would have assumed it returned an error or a load of blank characters, but it doesn't. So much kudos to Mudface. There was a similar posting from Nate about trimming down an =cell("filename",a1) request so that it only contained the filename rather than the whole path. More successfully than I, Nate managed this impressively by (ummmm) locating the "[" character in the full path, and putting it's position into a mid function, with the last part of the MID function counting for n cahracters where n equaled the full length of the cell filename function LESS the number returned from the earlier FIND function (ie counting exactly for the remainder of characters) takes a break My observation (not a criticism or anything) was that maybe that that last bit to determine the remaining characters to count for in the last bit of the MID function might not be neccessary based on Mudface's method per the above I am relatively new to the FIND function, so Nate's formula was fab. Ditto Mudface's. It just occured to me they could be "combined" to save a bit of typing. Hope this makes sense to anyone who's still reading ! Chris :-Q [ This Message was edited by: Chris Davison on 2002-02-28 12:25 ] Eddie, here's Nate's thread to which I was reffering.... hope it helps satisfy your curiosity more than I was able to !! http://www.mrexcel.com/board/viewtop...ic=864&forum=2 [ This Message was edited by: Chris Davison on 2002-02-28 12:32 ]