trim first seven charactors from a string

[Eddie G.]

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]

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]

Eddie,

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

Aladin

 

[Chris Davison]

=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

 

[Guest]

CHRIS,

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

 

[IML]

Perhaps something like,

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

 

[Mudface]

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]

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/viewtopic.php?topic=864&forum=2


This message was edited by Chris Davison on 2002-02-28 12:32