IF Function for column containing text

IF Function for column containing text - Excel 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

New User Judy - 20 Jul 2007 01:04 GMT

I have a Column of pricing from a csv. file that has TBA in some rows (price
to be announced). I would like to change TBA to a 0.

I have started with =IF(E1="TBA", ???

I also have a coded price column for cost price and would like to get rid of
the letter code and also change remaining price to a 2 decimal cost.

I've been reading and trying all kinds of things. I really need an IF
function for the first column and am not sure what I need for the second.

Any help really appreciated

Reply to this Message

T. Valko - 20 Jul 2007 04:11 GMT

For the TBA thing:

Select the column in question
Goto the menu Edit>Replace
Find what: TBA
Replace with: 0
Replace all
Close

For the letter code and decimal thing:

You'd need to post *several* representative samples so we can see what we're
dealing with.

Signature

Biff
Microsoft Excel MVP

>I have a Column of pricing from a csv. file that has TBA in some rows
>(price
[quoted text clipped - 10 lines]
>
> Any help really appreciated

Reply to this Message

T. Valko - 20 Jul 2007 06:51 GMT

Ooops!

I just realized you say you're using Excel 2007 so the Edit>Replace probably
isn't the same.

The basic procedure I described will do what you want it's just that I've
described it based on the locations of menu commands for a different version
of Excel (2002). You should be able to do the same thing but the locations
will be entirely different in Excel 2007.

Signature

Biff
Microsoft Excel MVP

> For the TBA thing:
>
[quoted text clipped - 24 lines]
>>
>> Any help really appreciated

Reply to this Message

Roger Govier - 21 Jul 2007 12:09 GMT

Hi
The only difference with XL2007, is you need to be on the Home tab.
At the far right, click on Find, the ceson option on dropdown is for
Replace, then follow Biff's instructions.

--
Roger Govier

> Ooops!
>
[quoted text clipped - 35 lines]
>>>
>>> Any help really appreciated

Reply to this Message

New User Judy - 26 Jul 2007 06:48 GMT

Thank you so much for replying to me. Have read all posts and will give more
examples of the 2 decimal thing when I get to work tomorrow. Really
appreciate the assistance. (Rec'd message saying I had received a reply and
it only took me about 15 minutes to find it!). I don't know the protocol
here - I don't want to clutter this site with thank yous to everyone but I do
thank everyone for replying. Let me know if I should reply to everyone or if
this just adds to the thread.
Thanks again - Judy

> For the TBA thing:
>
[quoted text clipped - 24 lines]
> >
> > Any help really appreciated

Reply to this Message

pdberger - 20 Jul 2007 23:08 GMT

NUJ --

T Velko is correct about the Replace function, at least in Excel03 (which I
have also). It'll probably work similarly in Ex07. However, if you wanted
to preserve the original data, you could create a new column using the =IF
statement you asked about. Here's one that would work for you:

=IF(E1="TBA",0,E1)

Tested, works fine.

HTH

> I have a Column of pricing from a csv. file that has TBA in some rows (price
> to be announced). I would like to change TBA to a 0.
[quoted text clipped - 8 lines]
>
> Any help really appreciated

Reply to this Message

New User Judy - 26 Jul 2007 22:10 GMT

Re: Decimal Thing.

I have a list in excel that goes like this:

Col: A B C D
E F
Part Number Part Description Unit, eg EA Prc Code List Price
Cost Price

Example:

Row:
1 921429 Choke Assembly EA 4 956
000006D59
2 921530 Rear Axle EA 0 TBA
3 922509 Front Fender EA 1
21559 000195A99
4 930492 Brake Set EA 1
6951 000063A19
5 942489 Cotter Pin EA 5
150 000000X75

I downloaded this price file from manufacturer (ASCCII format) and turned it
into a csv file so that I can upload it into our Software package at work.

What I should end up with is a list without the PRC Code column, the E (List
Price) column without TBA's and all prices at 2 decimal points and the F
column (Cost Price) decoded and at 2 decimal points. Example 195A99 =
$195.99, 63A19 = $63.19, etc.

When it was done before some instructions were left - maybe they will make
sense to you:
When in excell: Highlight all column. Click on Text. Insert Columns after
'List Price' and after 'Cost Price' to paste formula.
Change cell to General. Formula = IF(E1="TBA","",E1/100)

In G column Left(G1,6)&"."RIGHT(G1,2)
Then Calculate & delete formula columns.

It makes some sense to me but not enough to do it. When I tried above
formula everything was ok till I typed the / and the formula just went away.

Appreciate your thoughts on this - Thanks - Judy

> I have a Column of pricing from a csv. file that has TBA in some rows (price
> to be announced). I would like to change TBA to a 0.
[quoted text clipped - 8 lines]
>
> Any help really appreciated

Reply to this Message

Rate this thread: