Formula to extract alphanumeric to a different cell

1. 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

2. Re: Formula to extract alphanumeric to a different cell

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)

3. Re: Formula to extract alphanumeric to a different cell

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?

4. Re: Formula to extract alphanumeric to a different cell

Try these:

=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND(":",A2)+2,99)," ",REPT(" ",99)),98))

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),98))

5. Re: Formula to extract alphanumeric to a different cell

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.

6. Re: Formula to extract alphanumeric to a different cell

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

7. Re: Formula to extract alphanumeric to a different cell

Originally Posted by Confuze
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?
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 reference

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1