I am looking for a formula to extract a number from text
Have tried to use formula below but get #value!
Please Login or Register to view this content.
It would be appreciated if someone could provide me with the correct formula to do this
I am looking for a formula to extract a number from text
Have tried to use formula below but get #value!
Please Login or Register to view this content.
It would be appreciated if someone could provide me with the correct formula to do this
Try
=MID($A1,SEARCH(" Number ",$A1)+8,200)
SEARCH is not case-sensitive AND it finds the start of the string so add 8
FIND worked with "NUMBER" rather then "Number"
Here's another way:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99),3),99))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If it is always a 12 digit number and at the end of the text, then you could use this:
=RIGHT(A1,12)
Note that all the proposed solutions will return text values. If you want the value as a proper number then you need to convert it - this will do it:
=RIGHT(A1,12) +0
Hope this helps.
Pete
@Pete - I thought of that, but as these are account numbers, I suspect some may start with a 0, so converting to a real number value may not be what is required here.
I agree, but with just a single example to go on it is difficult to extrapolate what other data there may be.
Pete
Thanks for the help & input
A good way of saying thanks is to click on the "star" icon in the bottom left corner of any post that you have found to be helpful, thus Adding to the poster's Reputation.
Pete
Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks