I have a couple hundred rows of data where I need to change the format.
The format right now is one of these:
"01 01"; "01 02"; "01 03" or
"03AAP"; "03A02"; "03DDP"
I need them to look like this:
"0101"; "0102"; "0103" or
"03AP"; "0302"; "03DP"

Any recommendations? I use Excel 2003

Assuming all data have 5 character, no blank cells and that you only want the
first and last 2 characters:

=Left(cell address,2)&right(cell address,2)

Should you experience problem if the result would be read as a number, use
this:

=if(isnumber(left(cell address,2),text(left(cell address,2),"00"),left(cell
address,2)) &if(isnumber(right(cell address,2),text(right(cell
address,2),"00"),right(cell address,2))



marti wrote:
I have a couple hundred rows of data where I need to change the format.
The format right now is one of these:
"01 01"; "01 02"; "01 03" or
"03AAP"; "03A02"; "03DDP"
I need them to look like this:
"0101"; "0102"; "0103" or
"03AP"; "0302"; "03DP"

Any recommendations? I use Excel 2003

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200909/1

Just do a simple replace.

Highlight the rows you want affected.
Click Edit
Click Find and select the Replace tab
In the Find What slot hit the space bar once
In the Replace with slot, do nothing

Select Replace All

"marti" wrote:

I have a couple hundred rows of data where I need to change the format.
The format right now is one of these:
"01 01"; "01 02"; "01 03" or
"03AAP"; "03A02"; "03DDP"
I need them to look like this:
"0101"; "0102"; "0103" or
"03AP"; "0302"; "03DP"

Any recommendations? I use Excel 2003

If all that data is in a single column, then I'd insert a column directly to the
right of it and use a formula like:

=left(a1,2)&right(a1,2)
And drag down.

This should work if all your strings are 5 characters.



marti wrote:

I have a couple hundred rows of data where I need to change the format.
The format right now is one of these:
"01 01"; "01 02"; "01 03" or
"03AAP"; "03A02"; "03DDP"
I need them to look like this:
"0101"; "0102"; "0103" or
"03AP"; "0302"; "03DP"

Any recommendations? I use Excel 2003

--

Dave Peterson

Suppose your text is in A1 then

=REPLACE(A1,3,1,)

Copy down as necessary.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"marti" wrote:

I have a couple hundred rows of data where I need to change the format.
The format right now is one of these:
"01 01"; "01 02"; "01 03" or
"03AAP"; "03A02"; "03DDP"
I need them to look like this:
"0101"; "0102"; "0103" or
"03AP"; "0302"; "03DP"

Any recommendations? I use Excel 2003