Hello, I need to remove only numbers from excel cell - I have tried using the "constant" function but did not work. I have thousands of cells with a consistent 4 digit number like this:
1000 the rest is text
How can I remove only numbers but leave all text in the cell and then if I can trim the cell so there are not any spaces at the beginning afte removing the numbers. Any help would be much appreciated!
May be try like this...
In A1 Cell
1000 the rest is text
In B1 Cell
=MID(A1,5,5^6)
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
welcome to the forum. could you provide more egs for us to see a pattern? you mentioned it's always 4 digits & you want to remove spaces at the beginning after removing, so right now, my guess can only be:
=MID(A1,6,255)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
also this just in case there would be no spaces between the numebrs and text. a typo i prefer say 1000the rest is textconsistent 4 digit number
=TRIM(RIGHT(A1,(LEN(A1)-4)))
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
HI thank you - sorry I meant that all the numbers will be different and the text will be different for each cell - so it could be
1000 tlasdjfskldaf;
4008 aslkdfjasdlf;
4555 asldkfjas;d
what I meant is that it's consistently 4 digits-
what does a proper string look like?
=trim(mid(a1,5,255)) is another one
you could also use text to columns fixed width and skip the first column
"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
oh sorry I think that worked, however, I forgot to mention that not all cells have the numbers but just the text, so the only solution is running an =if(find((" but what would I indicate to find a number?
=if(isnumber(left(a1,4)+1),trim(mid(a1,5,255)),a1)
This should do it
=TRIM(REPLACE(A1,1,4,""))
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks