Excel 2003 -- Character spacing within the same cell

Hello there,

I am trying to apply a specific format and unfortunately not having luck --
HELP -- this is driving me nuts!

I tried to apply w/ a custom format and it doesn't work. How do you create a
custom format that has both zeroes and alpha characters that applies into the
same field. I need to create this for a custom import into another program.

This is what I am trying to do below:

Currently this is how it shows:

5BC 012345 67890 HEALTH 01-00-123459-00 << 1 space in between each area


5BC 012345 67890 HEALTH 01-00-123459-00 < There needs to be 3 spaces
applied after the 5BC, 1 space after the 012345, 3 spaces after the 67890,
and 2 spaces after the HEALTH.

Any suggestions to make this work the way I need it to to apply to whatever
field I choose this format and apply it to the fields without manually going
through over 330 lines of info that is similar but w/ different gl #'s and or
account #'s?

Any suggestions or how to's would be a beautiful thing!!

As I understand it, you already have the information typed into the workbook?
You just need to get it laid you differently based on the current entries?
Assuming it all takes exactly the same format now (3 characters, space, 6
characters, space, 5 characters, space, "HEALTH", space, 15 characters) then
put this formula in a cell on the same row. Assuming your info is in column A
starting at row 1 for now, adjust accordingly and column B is available for
use, in B1 put:

=LEFT(A1,3) & " " & MID(A1,5,6) & " " & MID(A1,12,5) & " " &
MID(A1,18,6) & " " & RIGHT(A1,15)

and extend the formula down the sheet.

"Danielle C" wrote:

> Hello there,
>
> I am trying to apply a specific format and unfortunately not having luck --
> HELP -- this is driving me nuts!
>
> I tried to apply w/ a custom format and it doesn't work. How do you create a
> custom format that has both zeroes and alpha characters that applies into the
> same field. I need to create this for a custom import into another program.
>
> This is what I am trying to do below:
>
> Currently this is how it shows:
>
> 5BC 012345 67890 HEALTH 01-00-123459-00 << 1 space in between each area
>
>
> 5BC 012345 67890 HEALTH 01-00-123459-00 < There needs to be 3 spaces
> applied after the 5BC, 1 space after the 012345, 3 spaces after the 67890,
> and 2 spaces after the HEALTH.
>
> Any suggestions to make this work the way I need it to to apply to whatever
> field I choose this format and apply it to the fields without manually going
> through over 330 lines of info that is similar but w/ different gl #'s and or
> account #'s?
>
> Any suggestions or how to's would be a beautiful thing!!
>

Maybe just adding another column and using a formula to do some changes:

=SUBSTITUTE(SUBSTITUTE(A1,"5BC ","5BC ")," HEALTH "," HEALTH ")

And drag down the column.

If you need to, you can select that helper column and do
edit|copy
followed by
Edit|Paste special|values
and remove the original column.

This formula looks for the exact 5BC and HEALTH characters--they must match
case, too.

Danielle C wrote:
>
> Hello there,
>
> I am trying to apply a specific format and unfortunately not having luck --
> HELP -- this is driving me nuts!
>
> I tried to apply w/ a custom format and it doesn't work. How do you create a
> custom format that has both zeroes and alpha characters that applies into the
> same field. I need to create this for a custom import into another program.
>
> This is what I am trying to do below:
>
> Currently this is how it shows:
>
> 5BC 012345 67890 HEALTH 01-00-123459-00 << 1 space in between each area
>
> 5BC 012345 67890 HEALTH 01-00-123459-00 < There needs to be 3 spaces
> applied after the 5BC, 1 space after the 012345, 3 spaces after the 67890,
> and 2 spaces after the HEALTH.
>
> Any suggestions to make this work the way I need it to to apply to whatever
> field I choose this format and apply it to the fields without manually going
> through over 330 lines of info that is similar but w/ different gl #'s and or
> account #'s?
>
> Any suggestions or how to's would be a beautiful thing!!

--

Dave Peterson