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

 

[aa2000]

Anybody have any ideas on how to do this?

I think I would have to split the text in a cell based on a delimiter (in this case a "space") and then only whats between the two spaces. Correct?

Cheers

 

[GTO]

Certainly not certain, but maybe:
Before:

Excel Workbook
	A	B	C	D	E
1	Column 1 is as follows	Header	Empty	Column2 is as follows
2	HighTemp			Experiment Vacuum 6	Ok
3	LowTemp			Experiment HighTemp 1	Ok
4	LowPressure			Experiment LowPressure 8	Ok
5	HighPressure			Experiment LowTemp 2	Not Ok
6	Vacuum			Experiment HighPressure 1	Ok
Sheet1

After:

Excel Workbook
	A	B	C	D	E
1	Column 1 is as follows	Header	Empty	Column2 is as follows
2	HighTemp	Ok		Experiment Vacuum 6	Ok
3	LowTemp	Not Ok		Experiment HighTemp 1	Ok
4	LowPressure	Ok		Experiment LowPressure 8	Ok
5	HighPressure	Ok		Experiment LowTemp 2	Not Ok
6	Vacuum	Ok		Experiment HighPressure 1	Ok
Sheet1

In a Standard Module:

Option Explicit
    
Sub exa()
Dim REX         As Object '<--- RegExp
Dim rngShort    As Range
Dim rngLong     As Range
Dim aryShort    As Variant
Dim aryLong     As Variant
Dim i           As Long
Dim ii          As Long
    
    Set REX = CreateObject("VBScript.RegExp")
    REX.Global = False
    REX.IgnoreCase = False
    
    '// Note: using worksheet's CodeName                                            //
    With Sheet1
        Set rngShort = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
        Set rngLong = Range(.Cells(2, 4), .Cells(.Rows.Count, 4).End(xlUp))
        
        aryShort = rngShort.Value
        aryLong = rngLong.Value
        
        For i = LBound(aryShort, 1) To UBound(aryShort, 1)
            REX.Pattern = "\b" & aryShort(i, 1) & "\b"
            For ii = LBound(aryLong, 1) To UBound(aryLong, 1)
                
                If REX.Test(aryLong(ii, 1)) Then
                    '// Presumes one row header                                     //
                    .Cells(i + 1, 2).Value = .Cells(ii + 1, 5).Value
                    Exit For
                End If
            Next
        Next
    End With
End Sub

 

[aa2000]

Thank you very much, this has got me going in the right direction and I can now adapt it to my project.

Thanks GTO!

 

[Peter_SSs]

I think I would have to split the text in a cell based on a delimiter (in this case a "space") and then only whats between the two spaces

To do this bit would be just like I suggested in your other thread only with a space as the delimiter. So

Sub ExtractText()
    Dim s As String, a As String
    
    s = "Experiment HighTemp 1"
    a = Split(s, " ")(1)
    MsgBox a
End Sub