CODEWITHSUNDEEP
excelexcel-formula
Gravemind
Gravemind

Reputation: 55

Excel: reformat/replace part of string in cell

I have an Excel file that contains descriptions:

Trees [1]
Buildings[20]
Cameras[303]

What I'm wanting to do is format what's inside the brackets to contain 4 digits. I know the custom format is "0000", but can I apply that format to those numbers?

The end result would look like:

Trees [0001]
Buildings[0020]
Cameras[0303]

Thank you for any tips!

Upvotes: 1

Views: 119

Answers (2)

zx8754
zx8754

Reputation: 56249

Assuming Trees[1] is on A1 cell, then try this formula:

=LEFT(A1,FIND("[",A1))&RIGHT("0000"&MID(A1,FIND("[",A1)+1,LEN(A1)-FIND("[",A1)-1),4)&"]"

Upvotes: 3

SeanC
SeanC

Reputation: 15923

I'm going to assume that your number is always in [], and those only occur once per string.

Using LEFT and MID to slice the string up, and then formatting the number, I end up with a formula that looks like this:

=LEFT(A1,FIND("[",A1)) & TEXT(VALUE(MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)),"0000") & "]"

Broken down:

=LEFT(A1,FIND("[",A1)) << grab all the letters up to the [

& then add in:

TEXT(VALUE(MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)),"0000") << the number (VALUE) found between the brackets (FIND), and then format that number (TEXT) to be the format you desire

& then add in:

"]" << the final close bracket

Upvotes: 2

Related Questions