I am trying to extract a number from a text string to multiply by a number value in another cell. I've attached the spreadsheet for your perusal (I think).
I am trying to extract a number from a text string to multiply by a number value in another cell. I've attached the spreadsheet for your perusal (I think).
Which cell contains the text string you're trying to extract the number from?
282E:290E are the cells
What bit of those cells do you want to extract? You know what you want, but you have not made it clear to us.
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.
I'm taking a guess at what you're looking for. If this is not what you want, please reply to AliGW's request for more information.
This relies on the text string being structured as it is in E282:E290. The formula may have to be adjusted if the structure of the text string changes.Please Login or Register to view this content.
Thanks McIvosh. I'll try to figure out what you did, but it works.
MID gives you a substring from a string, based on a starting point and a length. The formula looks for the "@" and adds 2 to get to the starting point, and then looks for the "'" character and subtracts the starting point to get the needed length. Hopefully that makes sense.
See Melvosh's post. He understood.
Is that meant to be a dig at me for asking for more detail?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
I was attempting the use of the RIGHT function (RIGHT(3)) to extract the number at the end of the column "E" and multiply by the number in the cell in the "G" column, but got the "Value" error because I couldn't get the "E" column number (text) formatted as number. Your solution works of course, but I don't know why it doesn't run up against the aforementioned problem.
You can sometimes get round the text issue:
=VALUE(RIGHT(text_string,3))
Or:
=—RIGHT(text_string,3)
OK. No digs intended. BTW, my wife and I visited a Saxon chapel west of London years ago. We met a charming English lady who was impressed that two Yanks took the time to visit this out of the way historic stone building. She was rather chagrined by our use of "OK", and told us it was not proper English, to which we both responded, automatically, "OK". Oops.
LOL!
Glad to have helped.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks