Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 16th 04, 05:51 PM
Jacky D.
 
Posts: n/a
Default 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   Report Post  
Old December 16th 04, 06:09 PM
Frank Kabel
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I easily convert a single column of text (multiple rows si. philmah Excel Discussion (Misc queries) 2 December 16th 04 12:24 AM
Is there a way I can enter a letter to proceed text a column of ce Gordon Excel Discussion (Misc queries) 3 December 13th 04 07:13 PM
I am trying to link based on a text value instead of cell position John Links and Linking in Excel 3 December 3rd 04 06:29 PM


All times are GMT +1. The time now is 03:15 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017