trim first seven charactors from a string

Eddie G.

Board Regular
Joined
Feb 27, 2002
Messages
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Use =MID(A1,9,LEN(A1)), where your text is in cell A1. This will remove the first 7 chrs and the space.
 
Upvote 0
Eddie,

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

Aladin
 
Upvote 0
=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
:)
 
Upvote 0
CHRIS,

MUDFACES'S FORMULA WORKED WELL, HOW WOULD YOU HAVE WRITTEN IT? I AM CURIOUS....THANKS ALOT...EDDIE G.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top