Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34

INSERT A SPACE INTO A TEXT VALUE

This is a discussion on INSERT A SPACE INTO A TEXT VALUE within the Excel Questions forums, part of the Question Forums category; Hi, Hope someone can help me. I have a number of text values. However, I need to be able to ...

  1. #1
    Board Regular
    Join Date
    May 2003
    Posts
    260

    Default INSERT A SPACE INTO A TEXT VALUE

    Hi,

    Hope someone can help me.

    I have a number of text values. However, I need to be able to add a space into these after the fifth character.

    Any ideas how I can do this?

    Thanks

    Jon

  2. #2
    Board Regular
    Join Date
    Jul 2003
    Location
    Manchester (UK)
    Posts
    4,432

    Default

    Hi,

    how about

    =LEFT(A1,5)&" "&RIGHT(A1,LEN(A1)-5)

    HTH

    Alan
    HTH

    Alan

    --------------------------------------------------------
    Vlookup not sufficient? Follow the link for latest version of FuzzyVLookup or RuleLookup .
    Alternatively to compare two worksheets try Compare Two Sheets
    --------------------------------------------------------
    There are 10 kinds of people - those who understand binary and those who don't

  3. #3
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,634

    Default Re: INSERT A SPACE INTO A TEXT VALUE

    Try:

    =REPLACE(A1,5,1,MID(A1,5,1)&" ")
    "Fair Winds and Following Seas"

  4. #4
    Board Regular
    Join Date
    May 2003
    Posts
    260

    Default Re: INSERT A SPACE INTO A TEXT VALUE

    Fantastic.

    Thanks a lot

    Jon

  5. #5
    New Member
    Join Date
    May 2011
    Posts
    3

    Question Re: INSERT A SPACE INTO A TEXT VALUE

    i want ask related to above question

    i have text example abc12ab
    how to make it abc.12.ab
    and like this too a8b6f4 how to make it like this a.8.b.6.f.4

    TIA

  6. #6
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,209

    Default Re: INSERT A SPACE INTO A TEXT VALUE

    For the original question:

    =TRIM(REPLACE(A1,6,0," "))
    Office 2007/2010

  7. #7
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,209

    Default Re: INSERT A SPACE INTO A TEXT VALUE

    colibri:

    Sheet1

     AB
    1abc12ababc.12.ab

    Spreadsheet Formulas
    CellFormula
    B1=REPLACE(REPLACE(A1,4,0,"."),7,0,".")


    Excel tables to the web >> Excel Jeanie HTML 4
    Office 2007/2010

  8. #8
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,209

    Default Re: INSERT A SPACE INTO A TEXT VALUE

    The second one, I would use a UDF:

    Code:
    Function adddots(r As String) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "(.)"
        .Global = True
        adddots = .Replace(r, "$1.")
        adddots = Left(adddots, Len(adddots) - 1)
    End With
    End Function
    Sheet1

     AB
    1a8b6f4a.8.b.6.f.4

    Spreadsheet Formulas
    CellFormula
    B1=adddots(A1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Office 2007/2010

  9. #9
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,209

    Default Re: INSERT A SPACE INTO A TEXT VALUE

    The second one, I would use a UDF:

    Code:
    Function adddots(r As String) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "(.)"
        .Global = True
        adddots = .Replace(r, "$1.")
        adddots = Left(adddots, Len(adddots) - 1)
    End With
    End Function
    Sheet1

     AB
    1a8b6f4a.8.b.6.f.4

    Spreadsheet Formulas
    CellFormula
    B1=adddots(A1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Office 2007/2010

  10. #10
    New Member
    Join Date
    May 2011
    Posts
    3

    Question Re: INSERT A SPACE INTO A TEXT VALUE

    ur answer working, thanks alot mr. HOTPEPPER
    sorry i want ask again (reverse with my question before)

    abc.12.ab how to make it abc12ab
    a.8.b.6.f.4 how to make it like this a8b6f4

    thank in advance

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com