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.

 

[Kazoo6613]

I think I might have worked it out with this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1:a10000")) Is Nothing Then
Application.EnableEvents = False
Range("B" & Target.Row).Value = Now
Application.EnableEvents = True
End If
End Sub

I am able to protect the rest of the spreadsheet - columns C,D,E,F- with A & B unprotected.
Thanks again to all for your help above.

 

[István Hirsch]

If the second ^ is always preceded by a space, but the first never (as in the example):

to extract the number:

=MID(A1,FIND(" ^",A1,20)+10,8)

the 2 letters:

=MID(A1,FIND(" ^",A1,20)+18,2)

 

[Snakehips]

Kazoo6613,

In the line where you are setting the format you have omitted the offset values so effectively it is formatting column A.

Try the following which will alow you to have column B locked.

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
ActiveSheet.Unprotect
Target.Offset(, 1) = Now
Target.Offset(, 1).NumberFormat = "dd/mmm/yyyy"
ActiveSheet.Protect
End If
End Sub

Hope that helps.

 

[Peter_SSs]

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

For what it's worth, aren't the red characters superfluous?

 

[István Hirsch]

Sorry, my #12 correctly:

to extract the number:

=MID(A1,FIND(" ^",A1)+10,8)

the 2 letters:

=MID(A1,FIND(" ^",A1)+18,2)

 

[mikerickson]

For what it's worth, aren't the red characters superfluous?

Yes.

 

[Kazoo6613]

Thank you all. Peter, your VBA code worked perfectly.

 

[Rick Rothstein]

Thank you all. Peter, your VBA code worked perfectly.

First off, Peter did not post any "code", Tony (Snakehips) did... is his the code you were referring to?

Second, did you ask two separate, independent questions in this thread (Tony's code seems to have nothing to do with the original question you asked)? If yes, please ask new questions in a new thread instead of jumping on the back of an existing thread (even if that existing thread is yours)... it will help people in the future who search for information in old threads if multiple solutions are not all jumbled up with each other in the way this thread appears to be... separate threads for separate questions assures that will not happen.