Extract text 8 characters after second ^

[Kazoo6613]

I've only recently found this forum and have found it wonderfully helpful.

I'm trying to extract text from the following string:

%B6100569849384652^GRAHAM/DAVID EXAMPLE ^180579911965022VNDAVID

The first thing I would like to do is extract the 8 digits (birthday) after the 8 digits after the 2^nd ^ (the digits I have highlighted in green).

The second thing I would like to do is extract the 2 letters after the green numbers (highlighted in blue)

So far with the help of this forum I have figured how to extract the name and the 10 digits to the left of the first ^,but now I'm stumped. Thanks to all.

Excel version 2010.

 

[shawnhet]

These two formulae should work (though your birthdate only has 7 numbers in your example)

=MID(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"^","*",LEN(A1)-LEN(SUBSTITUTE(A1,"^",""))))),9,7)
=MID(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"^","*",LEN(A1)-LEN(SUBSTITUTE(A1,"^",""))))),16,2)

Cheers,

 

[Rick Rothstein]

I see only 7 digits highlighted in green, not 8. Going with that, here is how to retrieve those 7 digits...

=MID(TRIM(MID(SUBSTITUTE(A1,"^",REPT(" ",999)),1999,999)),9,7)

And here is how to retrieve the two letters after the above 7-digit number...

=MID(TRIM(MID(SUBSTITUTE(A1,"^",REPT(" ",999)),1999,999)),16,2)

 

[mikerickson]

Try
=MID(A1, FIND("^", A1&"^^", FIND("^",A1&"^^")+1)+9, 8)
and
=MID(A1, FIND("^", A1&"^^", FIND("^",A1&"^^")+1)+17, 2)

 

[JRS1010]

Assuming your string is in A1: B1= MID(A1,FIND("^",A1,FIND("^",A1)+2)+1,8) and C1 = MID(A1,FIND("^",A1,FIND("^",A1)+2)+16,2)

 

[Kazoo6613]

The 7 digits was my mistake. I lost one by mistake. But I've tired Shawnhet's solutions, adjusting it for 8 digits and it works like a charm. I will try your solutions too, Rick. Thank you both.

 

[Kazoo6613]

Mikerikson, your solution worked too. Thank you.

Regarding the formula that extracts the birthdate (I've listed all 3 solutions from above for the birthdade formula)

=MID(RIGHT(A3,LEN(A3)-FIND("*",SUBSTITUTE(A3,"^","*",LEN(A3)-LEN(SUBSTITUTE(A3,"^",""))))),9,8)

=MID(TRIM(MID(SUBSTITUTE(A4,"^",REPT(" ",999)),1999,999)),9,8)

=MID(A5, FIND("^", A5&"^^", FIND("^",A5&"^^")+1)+9, 8)

which returns a number like: 19650522 (yyyymmdd)

Is there any way to format that to look like: 1963/05/22?

 

[barry houdini]

Using Mike's version you can amend like this to get a "true" date (i.e. the date serial number)

=TEXT(MID(A5, FIND("^", A5&"^^", FIND("^",A5&"^^")+1)+9, 8),"0000-00-00")+0

Now you can format that in any date format you want, e.g. yyyy/mm/dd

 

[Kazoo6613]

Thank you so much Barry et al. You've all been very helpful. Thank you.

 

[Kazoo6613]

Hello again. I am using a magnetic stripe reader to read cards. The card data goes in to a cell in column A.
I am running a VBA script to enter today's date into the adjacent cell in column B.
My script:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("a2:a10000")

If Not Intersect(Target, rng) Is Nothing Then
Target.Offset(, 1) = Now
Target.Offset.NumberFormat = "dd/mmm/yyyy"
End If
End Sub

When I have my sheet unprotected, it works great. I scan the card, the date gets entered and the date and other cells get populated based on the extracted data from column A.
When I protect my sheet (even leaving column B unlocked) I get the error:
"Run time error 1004. Unable to set the NumberFormat property of the Range class."

I would like to be able to protect the spreadsheet to keep the other columns from getting messed up.