Hello All,
Please help me to extract the number from the text string. Please see sample in attached
Regards,
tt3
Hello All,
Please help me to extract the number from the text string. Please see sample in attached
Regards,
tt3
Hi there. Your examples may be random. I hope they are... In your real strings - do you have things like 23GHY - with no space between the number and the letter?
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn,
Thank you for your time. Yes, because many user(s) create in their own way and the text string is their own description.
Regards,
tt3
OK. Try this. It's a bit of a monster 'cos it's a combination of a couple of things caused by your 23GHY problem and the problem caused by the hyphens. This is is an array formula and must be ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Do uyou need to use the numbers as numbers elsewhere? This formula returns them as text (which you can't add/subtract, etc). The problem is the 12-18 seqence. I'd need to make changes if you want them as real numbers. Anyway take a look and let me know. Solution in Col K. The other cols can be deleted. They're just intermediate steps, retained in case I didn't get it right first time.
a bit shorter
Nice approach. If you remove the helper columns - it's still a bit of a monster... albeit about 10 characters shorter than mine...
Yes, but it's not volatile
Hello All,
That's work but please look at sample again and revise the formula.
Regards,
tt3
Didn't look above approaches so mine is probably longer but here it is
That certainly makes life a lot simpler...
Last edited by Glenn Kennedy; 03-28-2015 at 04:19 AM. Reason: wrong attachment.
=MID(LEFT(A2,MAX(IF(ISNUMBER(MID(A2,ROW(1:256),1)+0),ROW(1:256)))),MIN(IF(ISNUMBER(MID(A2,ROW(1:256),1)+0),ROW(1:256))),256)
try this array formula, confirm with shift+Ctrl+Enter
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Inspired by ZborPlease Login or Register to view this content.
Array formula in B2 and copy down
=MID(LEFT(A2,MATCH(1,-MID(A2,ROW(A$1:A$99),1),1)),MIN(SEARCH(ROW(A$1:A$10)-1,A2&1/17)),99)
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Row\Col A B 1TEXT RESULT 2HMP CCD ABC 12-18DWA AAA 12-18 3HMP CCD 22-18D AAA 22-18 4ABC CCD BOB 24D NNC 24 5ABC CCD 28 D NNC 28 6ALA DND 16G EWSFFSD 16 7ALA DND 16DWA TRWW 16 8BBC DND 28 DWA GFHGFHGF 28 9ALA DND 14 G GFHFHGF 14 10ALA DND 22G DSA 22
this one is for both tables:
***Array formula
=LOOKUP("zzzzz",MID(LEFT(A2,MATCH(1,-MID(LEFT(A2,LEN(A2)-5),ROW(A$1:A$99),1))),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1/17)),ROW($1:$1000)))
Last edited by AlKey; 03-28-2015 at 09:35 AM.
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
Hello All,
All formula works but how to apply the below IF....to your Formula and I just need the first few number before D, G, DWA
Regards,Please Login or Register to view this content.
tt3
I am a little confused. Have you changed your requirements again? Can you repost a FINAL list of Before/After strings so that we can bring this to a close?
Thanks!!
Hi Glenn,
I'm not changing anything but there's a lot of rows doesn't have "DND" or "CCD" and I don't want result show "#N/A".
Regards,Please Login or Register to view this content.
tt3
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks