Excel Help - add prefix

[Dolby]

I have a list of product SKUs (numerals) going down a column - like 1001, 1002, 1003 etc

However I need differentiate by adding the initials DB infront of each - so it reads DB.1001, DB.1002 etc Any way to automate that at all?

Thanks!

 

[EmileS]

=CONCATENATE

 

[EmileS]

=CONCATENATE("DB.",Insert Cell number)

 

[Dolby]

Thank you!

 

[AlmightyBender]

Or even easier:
="DB."&A1

 

[Dolby]

Is there an opposite to that ?

I now have DB.1001 ... but need to remove the DB part?

 

[Pada]

The opposite is a bit more complex, because you have to
1) find the position of a character/string with =FIND
2) and then select a partial string with =MID

 

[satanboy]

=RIGHT(A1,4)

 

[yebocan]

try http://www.asap-utilities.com/ find them most helpful when riding the Excel beast

 

[Dolby]

Thanks guys!

 

[Hamish McPanji]

=RIGHT(A1,4)

Not a good idea to use absolutes in a situation like this. Assuming the length is always 4 is wrong

 

[MrR]

Is there an opposite to that ?

I now have DB.1001 ... but need to remove the DB part?

Select the column where the data is in, ctrl-H (Replace) and replace the value (DB) with ""
No formula needed and don't have to worry about absolutes going wonky.

 

[EmileS]

You can use Text-To-Columns option for that, use fixed width or text.

 

[PhreeMe]

Is there an opposite to that ?

I now have DB.1001 ... but need to remove the DB part?

Can use the =MID function as long as the DB. is a constant prefix.
Something like =MID(ref cell,4,*4)
The *4 can be changed to be technically as long as you like, assuming the numbers get longer than 4 digits.

 

[Hamish McPanji]

Can use the =MID function as long as the DB. is a constant prefix.
Something like =MID(ref cell,4,*4)
The *4 can be changed to be technically as long as you like, assuming the numbers get longer than 4 digits.

Can expand on this:

The equation below finds the fullstop "." in the value and then takes the value of anything after the "." . That way, it doesnt matter what is the prefix or the suffix, or their lengths, as long as there is "." somewhere in there.

To get the prefix : =MID(Cell#,1,FIND(".",Cell#)-1)

To get the suffix : =MID(Cell#,FIND(".",Cell#)+1,LEN(Cell#)-FIND(".",Cell#))

Replace all the 'Cell#' with the actual cell number where the value is e.g. A7
Then drag the formula down using the square in the bottom corner, and it will increment the cellnumber accordingly, so you can do it for the whole column/row

 

[charlh]

Or you could do custom formatting of the row or column and use the 'DB' as currency prefix...

 

[bekdik]

Or you could do custom formatting of the row or column and use the 'DB' as currency prefix...

This one. It doesn't change your data.

 

[PhreeMe]

The really simple option would just be "Find and replace".
Highlight the column you want to remove the prefix from, Ctrl - F, Replace, enter DB. in first field, leave second field blank...

But we like formulae

 

[mh348]

If you save as a csv, you can use an app called Csv'd, to add remove prefixes etc..