Formula to extract alphanumeric to a different cell
Good afternoon gurus,
I have over 2,000 of rows in a sheet. In cell A2 with a string of alphanumeric. What I would like to do is extract from cell A2 to a different cell that starts with 1X,2X,3X, and 4X (11digit long) to a different cell. Please help fix this code that can do more than one criteria.
=MID(A2,MIN(SEARCH("1X?",A2,1),SEARCH("1X?",A2,1)),11)
Thank you
If the strings are always 52 characters (I'm going to assume the two leading spaces in A2 & A3 are not usually there) and always the same structure then why not simply
B2:
Formula:
Please Login or Register to view this content.
C2:
Formula:Please Login or Register to view this content.
If there are leading or trailing spaces then instead of using just A2 in the formula substitute TRIM(A2)
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thanks, Richard, C2 works perfectly. For B2 the string are always not 52 characters long. Is there a formula that looks for a string start with 1X,2X,3x and so on?
Try these:
=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND(":",A2)+2,99)," ",REPT(" ",99)),98))
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),98))
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.
OMG!! Thank you both very much for your help. You guys save me tons of work by copy and paste over two thousand rows of data.
Try this
Enter formula in B2 and drag formula across to C2
Formula:Please Login or Register to view this content.
v A B C 1 Material 11dgits 8digits 2 Unrestricted-use 40 : 1XXXXXXXXXX 1628 0901 1KA12345 1XXXXXXXXXX 1KA12345 3 Unrestricted-use 40 : 2XXXXXXXXXX 1628 0902 1KB12345 4 Unrestricted-use 40 : 3XXXXXXXXXX 1628 0903 1KC12345 5 Unrestricted-use 40 : 4XXXXXXXXXX 1628 0904 1KD12345 6 Unrestricted-use 40 : 4XXXXXXXXXX 1628 0904 1KD12345
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
That's why I asked the question about the length. Your few examples were all 52 characters or 53 when a leading space was included which is why I suggested substituting TRIM(A2) for the A2 referenceOriginally Posted by Confuze
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks