Search in cell

[Paul Dennis]

Hi,

I have done a before and after to help

I have several columns of data. Column A is blank and I want to populate it
from column B but only when column B has "WI" as the first 2 characters.
If it does copy the contents from column from position 4 in the cell into
Column A. This column then needs to be populated down until the next WI is
found in column B.

Can you help?

BEFORE

Col A Col B Col C
WI: Ab23
John data
JIM data
WI: sd34

AFTER

Col A Col B Col C
Ab23 John data
Ab23 JIM data
sd34

 

[Bernard Liengme]

With the first WI:Ab23 in row 2
Use in A2: =IF(LEFT(B2,2)="WI",MID(B2,4,255),A1)
Copy down the column

With the first WI:Ab23 in row 1
In A1 use: =MID(B1,4,255)
Use in A2: =IF(LEFT(B2,2)="WI",MID(B2,4,255),A1)
Copy down the column

best wishes

 

[muddan madhu]

try this

leave first row blank.

In cell A2 put this formula and drag it down.

=IF(LEFT(B2,2)="WI",MID(B2,FIND(":",B2)+1,255),A1)

 

[Paul Dennis]

Thx for this, however I'm doing it in a Macro???

With the first WI:Ab23 in row 2
Use in A2: =IF(LEFT(B2,2)="WI",MID(B2,4,255),A1)
Copy down the column

With the first WI:Ab23 in row 1
In A1 use: =MID(B1,4,255)
Use in A2: =IF(LEFT(B2,2)="WI",MID(B2,4,255),A1)
Copy down the column

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[Paul Dennis]

No I mean I have a macro to do this, hence I can't actually put anything in a
call. I want to be able to call a macro to do the job.

 

[Bernard Liengme]

Sub tryme()
mylast = Cells(Cells.Rows.Count, "B").End(xlUp).Row
For j = 1 To mylast
If Mid(Cells(j, 2), 1, 2) = "WI" Then
Cells(j, 1) = Mid(Cells(j, 2), 4, 255)
Else
Cells(j, 1) = Cells(j - 1, 1)
End If
Next
End Sub