I have a cell that has a lot of text in it. It may look like the following:
The commonality is that the numbers are always followed by a space and PLN (such as " PLN") and preceded by a comma. Is there a way I can extract just the number? I can write a formula for something such as between parenthesis, but not for two different characters. The first comma is us
Please Login or Register to view this content.
Were you about to say that the first comma is not always the comma which indicates the start of the number?
Pete
If you're happy with a UDF, how aboutUsed like =GetNumber(A1)Please Login or Register to view this content.
Ordinary formula:
=-LOOKUP(1,-RIGHT(TRIM(LEFT(A2,FIND(" PLN",A2)-1)),{1,2,3,4,5,6,7,8,9}))
assumes data in A2.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Or this
Enter in B1 and copy down
Formula:
Please Login or Register to view this content.
Or this shorter version
Formula:Please Login or Register to view this content.
v A B 1 Widgets, Inc., 12345 PLN 5/29/2018; 12345 2 ABC-123: My Company, 8493 PLN 6/6/2018; 8493
Last edited by AlKey; 08-26-2018 at 04:28 PM.
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
Hi,
Use B1 formula in case you have "numbers" that start with 0s (zero) and you need to retain that format, result is Text.
Use C1 formula if you want the result converted to Real Numbers, leading 0s will be dropped.
Formulas accommodates "numbers" up to 99 characters in length.
Excel 2016 (Windows) 64 bit
Widgets, Inc., 12345 PLN 5/29/2018; 12345 ABC-123: My Company, 8493 PLN 6/6/2018; 8493 ABC-123: My some text Company, 008493 PLN more text 6/6/2018; 008493
Sheet: Sheet47
Excel 2016 (Windows) 64 bit
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("PLN",A1)-2)," ",REPT(" ",99)),99))
Sheet: Sheet47
Last edited by jtakw; 08-26-2018 at 05:19 PM.
Wow! Thank you for the responses! Here's replies back...
pete_uk: you are correct. Must have gotten distracted before I clicked submit!
fluff13: I like your VBA - I'll definitely add that to my code library, but was looking specifically for a formula in this instance.
glenn: Worked! Even on the cells I had that had commas before the number before PLN! (hopefully, you can follow along and understand that)
alkey: The longer one worked, but the short one didn't.
jtakw: Thanks for the input on the preceding zeros. This instance, I don't need to keep them around, but I'll definitely keep your formula handy as I'm sure I'll probably run into it again.
Thanks for the responses!
Then how about the C1 formula in my Post #6?Originally Posted by mainemojo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks