LinkBack URL
About LinkBacks
Hi I need help extracting a certain string of numbers and text from a string of text.
Example
String 12 3 4 abc4567 435 fgt 3.456 abcd
I need to extract abc4567 435
Is this possible please
Many thanks
John
Hi I need help extracting a certain string of numbers and text from a string of text.
Example
String 12 3 4 abc4567 435 fgt 3.456 abcd
I need to extract abc4567 435
Is this possible please
Many thanks
John
What's the logic behind why "abc4567 435" should be extracted from "String 12 3 4 abc4567 435 fgt 3.456 abcd"?
Will the desired result always be in-between the 4th and 6th spaces or something like that?
Hi John,
See if this type of formula works for you,
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),400,200))
Extract String using Space Rept and Trim.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
All the desired extracted will be 3 letters followed by 4 numbers a space and then another four numbers this will be absolute for each extract so example bcd4321 123 or gbg4567 655 the sequence could appear 4 characters into the string or 20 characters into the string.
Marvin, I tried the formula but it didn't return what I was looking for, cheers anyway mate
This (abc4567 435) is 3 letters followed by 4 numbers, a space, and then 3 numbers...Originally Posted by john dalton
I'm sure this was nothing more than a typo. Either way, this will probably require VBA.
If you want a better answer you need to give us more examples using an attached worksheet.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Here is an example attached
Cheers
John
Hi John,
Try this one
Formula:
Please Login or Register to view this content.
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 Alkey that's a great start, how would I adapt that if the text was different so and entry might say cde1234 567 is there a flexible way of looking for any 3 letters at the start of the sequence.
Cheers
John
If that (post #7) was a representative sample, then E5 ="abc1234 567" would work.
I'm sure this isn't the case.
Do the desired strings always come directly before " 279.000"?
If not, I suggest making your sample representative of your actual data.
Alkey, I think (post 4) its ANY three letters/4 numbers/space/3 numbers.
I can get the three numbers OK... but am struggling with the 7 character string....
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
Yes the abd1234 567 combination always appears before a 5 or 6 digit number with a decimal point in it (example 12.3456 or 1.2345)
Cheers
john
Try:
=RIGHT(LEFT(A1,SEARCH(".",A1)-7),11)
edit: no. won't work for 12.3456
If that's the case, then this would work:
E5 =LEFT(RIGHT(C5,20),11)
Again, this is assuming that the sample that you shared is representative of your actual data.
This should do it:
=RIGHT(TRIM(LEFT(A7,SEARCH(".",A7)-7)),11)
Hi Glenn, Absolute genius mate, works a treat the =RIGHT(TRIM(LEFT(A7,SEARCH(".",A7)-7)),11) formula is the one.
Many thanks to everyone on this much appreciated.
John
Glenn, that's on the money mate the =RIGHT(TRIM(LEFT(A7,SEARCH(".",A7)-7)),11) formula works brilliantly, thank you to everyone who contributed.
Much appreciated
John
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Try this
Enter formula in B1 and copy down
Formula:Please Login or Register to view this content.
With lookup table in D2:D3
v A B C D 1 String 12 3 4 abc4567 435 fgt 3.456 abcd abc4567 435 abc 2 String 12 3 4 cde4567 435 fgt 3.456 abcd cde4567 435 bcd 3 String 12 3 4 abc4567 435 fgt 3.456 abcd abc4567 435 cde 4 String 12 3 4 bcd4567 435 fgt 3.456 abcd bcd4567 435 5
Last edited by AlKey; 03-16-2018 at 10:11 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks