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!
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)&"]"
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
