How to extract user ID in between a text of varied length in Excel?

user_12988479 0
Viewed 154

I have to target ONLY the user ID from the middle of a text with varied length. If it's possible at all.

Scenario:

Here are 3 different information that I have in A2, B2, and C2

**A2** |  *BZ_PH0102~grahambenjamin.NBSC_BookDelivery_Print.09-01.22:42:06*    
**B2** |  *AS_P24~greenerobert.NBSC_BookDelivery_Print.09-01.23:32:43*    
**C2** |  *AS_P16~chernowron.NBSC_BookDelivery_Print.09-02.02:22:51*

I need to extract the different user IDs after the ~ and enter them in column D

For the sample above, it would be: grahambenjamin, greenerobert, and chernowron.

It would have been easy if the length is not too varied. Right now I'm only using =MID(A2,FIND("~",A2)+1,LEN(A2)-41-FIND("~",A2)-1) in D2.

The formula works ,but isn't completely correct. I just tried to experiment with it but I couldn't get the right one.

I also tried =LEFT(A4, SEARCH(".",A4)-1)

But only extract data left of .

Finally, I tried to add a right delimit, but it's not working properly as well.

=RIGHT(A3,SEARCH("~",A3))&LEFT(A3, SEARCH(".",A3)-1)

I'd appreciate any help. Thanks!

excel
1 Answers

Assuming you want everything between the first ~ and the first . then...

=MID(A22,FIND("~",A22)+1,FIND(".",A22)-FIND("~",A22)-1)

Explanation

MID( String , Starting Position , Number of Characters )

You already figured out how to find the starting point. Now we want to know how many characters to count to the right from that starting point.

Mathematically this would be the position of the period MINUS the position of the ~ and then MINUS 1 to offset the period. That calculation gives you the actual length of your string which we want to extract. So we are saying start at this position and count x many characters to the right.

user_67064190
Related