CODEWITHSUNDEEP
excelexcel-formula
user3827184
user3827184

Reputation: 1

add a formula to a cell without disrupting existing data

I am trying to use a formula to add data to a cell with existing data.

Someone else input data into my spreadsheet for me, and part of the data is missing. For example, it should read: P000051489 but instead it reads 051489.

Is there a formula I can write that will add the "P000" to the data without deleting or disrupting what has already been entered?

Upvotes: 0

Views: 1562

Answers (3)

AnotherParker
AnotherParker

Reputation: 790

Worksheet formulas in Excel must be entered into a specific cell, and they only affect the cell (or cells) that they're entered into.

However, you can write code in VBA that modifies the data (or formulas) in existing cells. It would be something like

Dim c as Range
For Each c in Range("A1:A10").Cells
   c.formula = "P000" + c.formula
next c

Is that the sort of solution you're interested in?

Upvotes: 0

gtwebb
gtwebb

Reputation: 3011

This depends if you want to actually change the value of the cell in which case the Concatenate or more simply ="P000" & A1 in cell B1 copy down and then paste value over.

Or if you want the cells to appear as P000051489 while only containing the value 051489 you could use custom formatting "P000"@. This method works well if you need to do things with the truncated value but tends to cause more issues since what you see if the cell doesn't match the value stored in it.

Upvotes: 1

Wabonano
Wabonano

Reputation: 128

You can use =CONCATENATE(TEXT1,TEXT2) http://office.microsoft.com/en-us/excel-help/concatenate-function-HP010062562.aspx

Upvotes: 1

Related Questions