Tags: 3x10, cell, cells, dm-1704-, dm17043x10, example, excel, format, formating, microsoft, msdn, numeric, software, spaces, text

how to format cells having text and numeric

On Microsoft » Microsoft Excel

1,529 words with 2 Comments; publish: Fri, 23 May 2008 18:33:00 GMT; (30662.50, « »)

Hi,

Please help me formating cells.

Example:

DM17043X10 - this is the text having in a cell

I need to format the above text as:

DM-1704- -3X10

spaces given between 4- & -3

Please help me on this.

Thank You,

PSL.

All Comments

Leave a comment...

  • 2 Comments
    • Hi PSL,

      You can't do this through formatting but, provided your data always have the

      same format, you can use a formula like the one below to reformat the

      strings:

      =LEFT(A1,2)&"-"&MID(A1,3,4)&"- -"&RIGHT(A1,4)

      --

      macropod

      [MVP - Microsoft Word]

      "PSL" <PSL.excel.itags.org.discussions.microsoft.com> wrote in message

      news:2E54783F-3844-479A-B94A-5C598D04B9CE.excel.itags.org.microsoft.com...

      > Hi,

      > Please help me formating cells.

      > Example:

      > DM17043X10 - this is the text having in a cell

      > I need to format the above text as:

      > DM-1704- -3X10

      > spaces given between 4- & -3

      > Please help me on this.

      > Thank You,

      > PSL.

      #1; Fri, 23 May 2008 18:34:00 GMT
    • You can't do this with a number format.

      But you could use a helper cell and a formula:

      =left(a1,2) & "-" & mid(a1,3,4) & "- -" & right(a1,4)

      This may work ok if all the data is really 10 characters wide.

      PSL wrote:

      > Hi,

      > Please help me formating cells.

      > Example:

      > DM17043X10 - this is the text having in a cell

      > I need to format the above text as:

      > DM-1704- -3X10

      > spaces given between 4- & -3

      > Please help me on this.

      > Thank You,

      > PSL.

      --

      Dave Peterson

      #2; Fri, 23 May 2008 18:35:00 GMT