Hello,
I have hundreds of rows of text that all have a unique ID. What I mean is each cell has a name and then before the extension has a ^PRP######. These are all filenames with a unique id attached before the extension.
What i wanted is to strip the ^PRP###### from each cell
is that possible?
thanks (see attached)
Last edited by gurp99; 01-29-2011 at 06:39 PM.
maybe this:
=MID(A1, FIND("^PRP", A1&"^PRP"), 100)
Never use Merged Cells in Excel
This formula copied down should do what you need....
=LEFT(A1,FIND("^",A1)-1)
Cheers
Jim
if you want to remove the last bit then just find ^* replace with nothing
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I actually needed to keep the extension. Thats the problem I was having, how can i just strip the Unqiue ID and leave the extension there?
find ^????????? replace with nothing
yes that is correctOriginally Posted by martindwilson
that's it do just that
the ?? are wild cards and i assumed you always had a 9 digit number hence 9 times?
so in find put
^?????????
replace with nothing
=RIGHT(A1,LEN(A1)-FIND("^",A1)) and drag down
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
with your sample.....is this what you're looking for?
Just realized you would like to keep this ^ as well as the number the attached does that
That actually works perfectly
thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks