couple of newbie questions

I have opened up a .csv file in excel and i have a couple of questions. There is a long column in this file with a mixture of text and numbers.

Can i somehow select all the cells in this column containing numbers and add a percentage of 10% to all?

Also in this file there are entries that look like this:

"Dimensions:w600mm x d220mm - Price £126"

Is there any way that i can pinpoint the price in this string and increase the value by 10% also?

ps, i realise that this is not really the correct way to do things in excel but the file has been exported from Actinic E-commerce software and the format needs to remain the same as i intend to import the ammended file.

many thanks
Alistair

You can add a helper column to do these calculations. Assuming your data is in column B, enter this formula in a new Column C:

=IF(ISNUMBER(B2),B2+(B2*0.1),LEFT(B2,FIND("$",B2))&RIGHT(B2,(LEN(B2)-FIND("$",B2)))+(RIGHT(B2,(LEN(B2)-FIND("$",B2)))*0.1))

This first checks to see if the value in B is a number, if it is, it adds 10% (100 becomes 110), if not, it looks for "$" (sorry I don't have Pound sign) and returns the text up to the $ then concatenate the value after (to the right) and adds 10% to it.

"Dimensions:w600mm x d220mm - Price £126" becomes

"Dimensions:w600mm x d220mm - Price £138.6"


You should be able to then take this column, copy/paste special...values to remove the formulas. This can then be re-exported to your .csv file.

HTH

Bruce

That is exactly what i was wanting! There are also cells in this column which don't contain either and it comes up with "#VALUE!".

Is there a way to keep these cells the way they were in column B?

Also, once this new column is fully created is it possible to delete the old column and keep my new prices so that it appears like the original file.

One more thing is that the numbers are not rounded up, they have a decimal point which is not wanted.

Youv'e been a great help, i had doubts whether this was possible

All we need to do is add a test for the 'pound' sign ($ to me), if it is not there just return the cell as it is:

=IF(ISNUMBER(B7),B7+(B7*0.1),IF(ISERROR(FIND("$",B7)),B7,LEFT(B7,FIND("$",B7))&RIGHT(B7,(LEN(B7)-FIND("$",B7)))+(RIGHT(B7,(LEN(B7)-FIND("$",B7)))*0.1)))

"Dimensions:w600mm x d220mm - Price £126" becomes
"Dimensions:w600mm x d220mm - Price £138.6"

$100 becomes $110

John Smith becomes John Smith

--------------

Yes, you can delete the original column *AFTER* you do the copy/paste special..values to remove the formulas in Col C

HTH

Bruce

that truly is incredible

Numbers rounded to nearest 100, text entries to nearest pound:

=IF(ISNUMBER(B12),ROUND(B12+(B12*0.1),-2),IF(ISERROR(FIND("$",B12)),B12,LEFT(B12,FIND("$",B12))&ROUND(RIGHT(B12,(LEN(B12)-FIND("$",B12)))+(RIGHT(B12,(LEN(B12)-FIND("$",B12)))*0.1),0)))

Anything else?



Bruce

I think we have pretty much reached the limits of what we can accomplish within a single formula. Based on your two new samples, I think your best course of action is to manually manipulate these entries to meet the formulary criteria.

Let's start by inserting a new, blank column to the right of data list (for now, let's assume your data list is in column B, insert a blank column C)

Place the cursor in the first cell with the extended entry. In your first example:

Dimensions:w250mm x d320mm - Price £134;Hand Choice:Left

Note that the data to the right of the ';' is not needed. We can use Text to Columns to strip that data away. If you are not familiar with TTC, on the main menu, click Data>Text To Columns... in the first window that pops up, select Delimited, click Next, put a check in the semicolon box and click Finish. Cell B will have:

Dimensions:w250mm x d320mm - Price £134 (and the formula now works) and Cell C will have:

Hand Choice:Left this can be deleted or just ignore it.

In your second example:

220mm depth:w150mm x d220mm - Price £10;320mm/520mm depth:w150mm x d320mm - Price £10

we will do the exact same steps, except after the TTC, you will need to move the result in column C to column B in a new, blank row (inserted below present cell).

Copy the formula down for this new row. Both cells will now calculate correctly.

I am hoping you don't have a lot of this type of entry in your data list. If so, can your sort your list? We can use another column to count the number of characters in a cell [=len(B2] and use that value to sort the data. This will put all of the long entries together, then only one TTC step would be needed to do the entire range. You would, however, still need to insert rows for those values in Col C from example #2 that need to be moved to B and evaluated.

This is a huge project you have. The steps seem long and complicated, but your data is so diverse that there is no "one size fits all" action to meet your needs.

TIP: Always save a backup of your data, just in case something doesn't work as planned, you can always go back and start again. I would save early and often (after each step or two), changing the file name each time e.g. MyDataV1.xls; MyDataV2.xls; MyDataV3.xls, etc.

Good Luck

Bruce

Try this formula on the "double entry" lines:

=LEFT(B6,FIND("$",B6))&ROUND((MID(B6,FIND("$",B6,1)+1,2)+MID(B6,FIND("$",B6,1)+1,2)*0.1),0)&MID(B6,FIND(";",B6)+0,(LEN(B6)-(FIND("$",B6,FIND(";",B6))-FIND(";",B6)-2)))&ROUND(MID(B6,FIND("$",B6,FIND(";",B6)),LEN(B6)-FIND("$",B6,FIND(";",B6))+1)+MID(B6,FIND("$",B6,FIND(";",B6)),LEN(B6)-FIND("$",B6,FIND(";",B6))+1)*0.1,0)

this will take:

220mm depth:w150mm x d220mm - Price $11;320mm/520mm depth:w150mm x d320mm - Price $11

and return:

220mm depth:w150mm x d220mm - Price $12;320mm/520mm depth:w150mm x d320mm - Price $12

Note in my sample I used $11 and with rounding, +10% returns $12

I hope this helps

Bruce

<blush> Thanks, Alistair. I don't know about 'genius'. I just try to figure out solutions that work. I'm glad I was able to help. Thanks for the feedback, it is always appreciated. </blush>

Cheers!

Bruce