Adding text in front of text already in a column in EXCEL? 1

[Bytor2000]

Hello all

I have been banging my head with this for 4 hrs. I have searched all over the net and still can not find the answer.

I have a spreadsheet that is filled with product data ( part number, description prices etc. ). What I want to do is add a 3 letter code in front of their part numbers so when I add this to the database on my server I will know which company owns the products.

Here is an example of what I want. In the column that has the product codes (part numbers) it would show something like this AGP4025 or PTC858 in all of the cells in that column. I want to add PCI- in front of every product code (part number) in the entire column. This is to let me know which company to order the parts from.

How would I do this?

Also on the prices column I would like to add my mark up of a certain percentage to all of the prices in that column. E.G. say 50% to all prices in that column.

How would I do this?

Any help would be greatly appreciated.

 

[SkipVought]

Hi,

Create a new column

="PCI"&A2

copy down thru all rows
copy this column
paste special - values in original column

VOLA!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Mikeb1970]

Hi Bytor,

Not completely sure i understand what you mean, but i'll take a shot

A1 = 1256
B1 = PCI
C1 = CONCATENATE(A1;B1) will give you 1256PCI in cell C3

you can reverse order (=CONCATENATE(B1;A1)) and use up to 30 cells.

next question

letts say cell D1 holds price

D1 = $234
then E1 = 1,5 * D1 (adding 50%)
well give you $351 in cell E1

if this doesn't work at first try check your cell formats, since allot of people make mistakes there

With regards

Mike

 

[Bytor2000]

Hello

I am sorry but those did not work.

This is what worked in one cell only:

=A1&""&B2&""

This combines the PCI- in cell A1 (which I added) with whatever text is in cell B2. When I drag the code down it keeps adding in the next cell with the last so each cell on down the column gets bigger e.g. cell A2 has PCI-ABC123 cell A3 has PCI-ABC123ABC124 next cell down has PCI-ABC123ABC124ABC125 ect.

Also I forgot to mention that in the end when I am done I have to delete any new columns made so that the spreadsheet is a certain number of columns. If I add columns when I upload to MySql it will put the info in the wrong place. So there can only be a set number of columns when I am done.

This is where all of the problems are.

 

[SkipVought]

What cell is your formula in?

Please post a few rows of sample data.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[KenWright]

Skips answer should have fixed you up perfectly.

Assume your data is all in Col B starting in row 2 so your first entry to adjust is in B2.
Pick a blank column to act as a helper column (let's assume Col J).
In J2 put the following formula exactly as stated and nothing else:-

="PCI-"&B2

Now copy cell J2, select J3 down to the last row necessary and paste. You should now see all the data that was in Col B in Col J but with PCI- in front of it.

Now select J2:J100 or whatever the last cell with data in Col J was. Do Edit / Copy and then click onto cell B2 and do Edit / Paste Special / Values.

You can now delete Col J and you are done.

As for the 50% bit - Type 1.5 in any empty cell and then select and copy that cell. Now select the entire range of values you wish to mark up and do Edit / Paste Special / Multiply. Done.

Regards
Ken...............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

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

 

[Bytor2000]

Thank you to Ken.

I guess the syntax was wrong on the earlier post by Skip.

Ken you are a life saver. Your post did everything perfectly.

Much appreciation being sent your way Ken!!!!

 

[KenWright]

Skip's advice was spot on, with the only thing not in his post being the hyphen, but that wouldn't have prevented it working.

What you had done was not 'lock' the cell A1 ie $A$1, so when you put =A1&""&B2&"" in the cell and copied down, it became

=A1&""&B2&""
=A2&""&B3&""
=A3&""&B4&""
=A4&""&B5&""

This meant you were adding the result of the previous result to the current. If you had locked the reference it would have worked fine, though you didn't need all those ""s

Skip's note had intended you to put ="PCI"&A2 in say B2 and then copy down. Each entry would then be whatever was in the cell with PCI tagged on the front, which was what you were after

Regards
Ken.............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

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

 

[loyal]

I am looking for a formula to reverse the numbers in a cell. Example: cell shows 12354 I want it to be 45321. How can I do this?

Thanks
Shelby

 

[SkipVought]

Shelby,

Please post this question in a new thread.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]