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

Thread: INSERT A SPACE INTO A TEXT VALUE

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

    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,479

    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,637

    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
    336

    Default Re: INSERT A SPACE INTO A TEXT VALUE

    Fantastic.

    Thanks a lot

    Jon

  5. #5

    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
    18,906

    Default Re: INSERT A SPACE INTO A TEXT VALUE

    For the original question:

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

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

    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 2010/2016

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

    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 2010/2016

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

    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 2010/2016

  10. #10

    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

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