Find Text in Cell and Copy

[Rob]

Hello,

I am trying to find some sort of code that will ease the pain of my task.
What I am needing to do is find a certain string of text within a cell and
copy it to another cell. The catch is that the text is variable in it total
content but the info I need is similar but not exactly the same nearly all
the time. Example below.

The text in cell (B2)...
Q_6464694_QT-WR9395.0435_8586-44.05950.3_QT-WR9395

There are litterally thousands of cells with similar text but the length of
it and the contect is variable.

What I need from it for cell (A2)...
QT-WR9395.0435

The "QT-WR" is always the same but the "9395" is variable in text and
length. The "." is always there somewhere and the "0435" is variable in text
only.


Is there a way to code something that looks for the QT-WR and grabs that
plus everything after it until the forth caracter after the "."? I tried to
use the Left and Right but I could not figure out how to make it give me the
right info since the text length is not always the same.


Thanks in Advance,
Rob

 

[Joel]

Try something like this

Sub SeperateString()

SearchStr = "QT-WR"
Data = "Q-6464694_QT-WR9395.0435_8586-44.05950.3_QT-WR9395"
Do While InStr(Data, "_") > 0
FirstID = Left(Data, InStr(Data, "_") - 1)
Data = Mid(Data, InStr(Data, "_") + 1)
If Left(FirstID, Len(SearchStr)) = SearchStr Then
'enter Your code here
End If
Loop

'Add more code here for last item

End Sub

 

[Rob]

Thanks for the reply. But I do not understand the "Data" portion of this.
The "Data being searched through is going to always be different from one
cell to another. It looks like with tat "Data" portion it only allows for
just the one. Is that correct? How can I make this to search other cells
insead of just the one?


Thanks Very Much Again.

 

[Joel]

You need to add code to get data from cells such as code below. I just
showed how to extract the strings from the data.

for Rowcount = 1 to 100
Data = Range("A" & RowCount)
'my code
next Rowcount

 

[Rob]

OK. I guess I'm a tad bit lost. This finds the information but how do it get
it to provide and extract the information I want found? Out of that Data
string the end result I want extracted for me to use is "QT-WR9395.0435"

How can I get that one particular restult to be able to be used instead of
just identifying that it is there?

Sorry for not understanding how to do this.

 

[Joel]

Look at my original code below. the IF statement does the trick

SearchStr = "QT-WR"
Data = "Q-6464694_QT-WR9395.0435_8586-44.05950.3_QT-WR9395"
Do While InStr(Data, "_") > 0
FirstID = Left(Data, InStr(Data, "_") - 1)
Data = Mid(Data, InStr(Data, "_") + 1)
If Left(FirstID, Len(SearchStr)) = SearchStr Then
'enter Your code here
End If
Loop

 

[Rob]

Ahhh... I have that now. Thanks. So now that I can display the info I need
via a MsgBox How then do I get it to take that place the info into another
cell that right next to that cell that the info came form?

Again, I must apologize for being a bugger.

 

[Rob]

NeverMind. I actually figured it our by a chance of luck.

THANK YOU so Much for ALL your help!!!

 

[Rob]

Hello Again,

I now have a twist to this and I was wondering if you can help with it. The
twist is... is that I am now having strings that have multiple possibilities
in the data and I only need the very first one. Here's the example with your
code being used....

SearchStr = "QT-WR"
Data =
"Q-6464694_QT-WR9395.0435_8586-44.05950.3_QT-WR9395|QT-WR9395.0528_8586-44.05950.7_QT-WR9395|QT-WR9395.0695_8586-44.05950.9_QT-WR9395"
Do While InStr(Data, "_") > 0
FirstID = Left(Data, InStr(Data, "_") - 1)
Data = Mid(Data, InStr(Data, "_") + 1)
If Left(FirstID, Len(SearchStr)) = SearchStr Then
MsgBox("FirstID")
End If
Loop


With the above I receive three answers in the order that they are listed but
I only want/need the very first one.

Thanks Once Again.
Rob

 

[Bernie Deitrick]

Rob,

You can simply use a goto to get out of the loop:

Do While InStr(Data, "_") > 0
FirstID = Left(Data, InStr(Data, "_") - 1)
Data = Mid(Data, InStr(Data, "_") + 1)
If Left(FirstID, Len(SearchStr)) = SearchStr Then
MsgBox("FirstID")
Goto FirstFound
End If
Loop

FirstFound:
'Rest of code

HTH,
Bernie
MS Excel MVP

 

[Rob]

It Works and yet it was so simple. Thank You Very Much!