| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search in cell
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search in cell
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search in cell
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) On Oct 21, 6:34*pm, Paul Dennis wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search in cell
Thx for this, however I'm doing it in a Macro???
"Bernard Liengme" wrote: 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search in cell
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. "muddan madhu" wrote: 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) On Oct 21, 6:34 pm, Paul Dennis wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search in cell
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Paul Dennis" wrote in message ... Thx for this, however I'm doing it in a Macro??? "Bernard Liengme" wrote: 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search for a cell. | Excel Discussion (Misc queries) | |||
| FIND / SEARCH text compare cell to string in 3rd cell | Excel Discussion (Misc queries) | |||
| Search and go to cell | Excel Worksheet Functions | |||
| How to search for text within a cell? | Excel Discussion (Misc queries) | |||
| format cell to search text in another cell | Excel Discussion (Misc queries) | |||
Linear Mode
