CODEWITHSUNDEEP
excelexcel-formula
SkysLastChance
SkysLastChance

Reputation: 221

Removing and Adding Characters to a cell

I was wondering if it is possible to remove the first 2 numbers from 8200001 and then chance it to 90 instead in ONE formula?

Example

8200001 = 9000001 
5822581 = 9022581
9688888 = 9088888

Upvotes: 2

Views: 78

Answers (2)

CallumDA
CallumDA

Reputation: 12113

The REPLACE function is perfect here:

=REPLACE(A1,1,2,90)

1 is where to start from (the position in your string)
2 is the number of digits to replace
90 is what to replace them with

This returns a string, so if you want a number add -- before the function:

=--REPLACE(A1,1,2,90)

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152450

Yes, use MID to parse:

=--(90&MID(A1,3,LEN(A1)))

Upvotes: 3

Related Questions