+ Reply to Thread
Results 1 to 4 of 4

Inserting space in a field

  1. #1
    Registered User
    Join Date
    02-27-2009
    Location
    chennai, India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Inserting space in a field

    Dear All,
    I have a column with the following typical data:
    1234-345-abc-435
    2345-432-ab-342

    My objective is to have four letters in the abc. If there is 3 or 2 letters as in this example (abc & ab) It has to introduce space like below: so as maintain 4 letters

    1234-345-abc -435
    2345-432-ab -342

    Can someone advice me how to do this.

    Thanks in advance
    sms

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Inserting space in a field

    Try this user defined function.

    Please Login or Register  to view this content.
    Paste this into a new module in the VBA editor (Alt F11) and add =InsertSpaces(A1) to A2 for example.
    Martin

  3. #3
    Registered User
    Join Date
    02-27-2009
    Location
    chennai, India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Inserting space in a field

    Thankyou. I also tried this formula. =REPLACE(A3,12,1,MID(A3,12,1)&" "). It is solved.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Inserting space in a field

    Quote Originally Posted by smselvi
    [I also tried this formula. =REPLACE(A3,12,1,MID(A3,12,1)&" ")
    If you're saying the string should be 17 digits and the string has the pattern of:

    ####-###-????-###
    where only the ???? varies in length but should always be 4 then in terms of a "one formula for all":

    Please Login or Register  to view this content.
    the above would work on both strings you specify

    In UDF terms you could perhaps use something like:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1