Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Putting text in a column based on variable text from another colum
I started a simple excel list. At this point, I have over 20000+ entries, and
I just realized that text in column A should actually be placed in another column. For example, If column A has the following values: T16256 F56236 G89562 I would want to see text such as Tooling Fixture Gauge in column F and have that letter in Column A taken out. The other problem is, not all of the cells in column A start with the alpha character, some are numeric only, and others have alpha characters after position 1 that I would not want to include. I'm not exactly profecient in Excel, I think I can somehow do this with a multi-step conditional format or validation of the columns, but I'm just not sure where to start. I'm hoping this doesn't require the writing of a macro, I'm a little lost here. Can anyone help point me in the right direction, I would be very grateful. Thanks, Jacky D. |
#2
|
|||
|
|||
Hi
1. Enter the following formulas: in B1 enter: =IF(ISNUMBER(--LEFT(A1,1)),A1,MID(A1,2,255)) in C1 enter =IF(ISNUMBER(--LEFT(A1,1)),"",IF(LEFT(A1,1)="T","Tooling",IF(LEFT (A1,1)="F","Fixture",IF(LEFT(A1,1)="G","Gauge","no t defined")))) 2. Now copy both formulas down for all rows 3. Copy these two columns and use 'Edit - Paste Special - Values' to remove the formulas 4. Now you can delete column A -- Regards Frank Kabel Frankfurt, Germany "Jacky D." schrieb im Newsbeitrag ... I started a simple excel list. At this point, I have over 20000+ entries, and I just realized that text in column A should actually be placed in another column. For example, If column A has the following values: T16256 F56236 G89562 I would want to see text such as Tooling Fixture Gauge in column F and have that letter in Column A taken out. The other problem is, not all of the cells in column A start with the alpha character, some are numeric only, and others have alpha characters after position 1 that I would not want to include. I'm not exactly profecient in Excel, I think I can somehow do this with a multi-step conditional format or validation of the columns, but I'm just not sure where to start. I'm hoping this doesn't require the writing of a macro, I'm a little lost here. Can anyone help point me in the right direction, I would be very grateful. Thanks, Jacky D. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I easily convert a single column of text (multiple rows si. | Excel Discussion (Misc queries) | |||
Is there a way I can enter a letter to proceed text a column of ce | Excel Discussion (Misc queries) | |||
I am trying to link based on a text value instead of cell position | Links and Linking in Excel |