Splitting text and reading parts of it

[aa2000]

Hi all

So I have two columns in separate workbooks. In the first column there is the short name of the experiment, and in the second one the full name of the experiment. Next to the full name there is a result that I want to put in a cell next to the short name. I need help trying to write the macro to match the two names.
The format is this:

Column1: ShortName

Column2: Experiment ShortName Number

So how can I get the macro to split the text in column2 and look at the part in between the 2 spaces? (ie the short name)

Cheers

 

[Dannyh1]

Hi,

This can be done, but why not seperate the text on using text to columns & then use straight forward vlookup?

 

[aa2000]

The text is imported into excel by another macro from the txt files where the data is stored. Since there around 1000 experiments, adding to that macro would slow the importing of the data down quite a bit. So I though this method may be better to find the matching experiments.

 

[Dannyh1]

Hi Try,

=VALUE(RIGHT(VLOOKUP("*"&A1&"*",[book2]Sheet1!$A:$A,1,FALSE),LEN(VLOOKUP("*"&A1&"*",[book2]Sheet1!$A:$A,1,FALSE))-FIND(" ",VLOOKUP("*"&A1&"*",[book2]Sheet1!$A:$A,1,FALSE),FIND(" ",VLOOKUP("*"&A1&"*",[book2]Sheet1!$A:$A,1,FALSE))+1)))

 

[aa2000]

Sorry Danny, I meant to say that I'd like to incorporate this into an existing macro, rather than as a formula. Sorry for confusion.

Is there anyway to modify this code to work:

Dim a As Integer
Dim s As String

For a = 7 To 10
s = Sheet3.Cells(a, 1).Value
If Sheet3.Cells(a, 1) <> "" Then s = Left(s, InStr(s, " ") - 1)

This sets s as a cell in a range and if the cell is not blank then it sets s as whatever is left of the space in the cell. How can this be changed to set s as what is in the middle of the 2 spaces?

Thanks

 

[aa2000]

Ok I have some code which splits the data in a cell, but this is not perfect and maybe overly complex.

Here it is

Sub split()

Dim a As Integer
Dim s As String

For a = 1 To 1000
s = Sheet5.Cells(a, 10).Value

If Sheet5.Cells(a, 10).Value Like "FESTKENNLINIE*" Then
If Sheet5.Cells(a, 1) <> "" Then
s = Right(s, InStr(s, " ") - 3)
s = Left(s, InStr(s, " ") - 1)
End If
Next a
End Sub

Is there a simpler way of doing this?

Cheers

 

[GTO]

Some actual sample data of both short and long data, as well as some data lines from the text file (faked up if sensitive) would likely be helpful.

 

[aa2000]

Ok heres an example of the data (fake, but in the same format)

Column 1 is as follows
HighTemp
LowTemp
LowPressure
HighPressure
Vacuum

Column2 is as follows
Experiment HighTemp 1 OK
Experiment LowTemp 2 NOT OK
Experiment LowPressure 8 OK
Experiment HighPressure 1 OK
Experiment Vacuum 6 NOT OK

Basically I need the value in the cell next to the ones in Column 2 (either ok or not ok) to be placed in cells next to the corresponding values in Column 1.

Placing the values won't be a problem, but how can I get the macro to look at only the text between the 2 spaces in column2?

Cheers

 

[GTO]

Firstly, apologies, as there is no textfile involved. I mis-read.

Maybe my thick skull, but I am still not following this exact part. Which file (a sample wb name) has has data in Col 1 and which in Col 2? Which wb is doing the looking? Does that make sense?

 

[aa2000]

The looking part is not in this macro, as that part I am able to do fairly easily, the code I posted is only to look through column 2 and try and find the information between the 2 spaces.

So for example in the first line of column2 it would look through and give s as being HighTemp since it is between the 2 spaces.

However can this be done more easily?

Sorry for the confusion!