+ Reply to Thread
Results 1 to 9 of 9

Formula Remove slashes, insert X in front of first character and remove last to characters

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,129

    Formula Remove slashes, insert X in front of first character and remove last to characters

    I have text in Col A1 eg 2001/003285/96


    I would like a formula to remove the slashes (/) , insert X in front of first Character and remove last to characters in B1

    It should look like this X2001003285 once extracted


    Your assistance is most appreciated

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799

    Re: Formula Remove slashes, insert X in front of first character and remove last to charac

    Hi Howard, try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,514

    Re: Formula Remove slashes, insert X in front of first character and remove last to charac

    Please try

    ="X"&LEFT(SUBSTITUTE(A1,"/",),10)
    for Fixed 10 digit

    or
    ="X"&SUBSTITUTE(LEFT(SUBSTITUTE(A1,"/",REPT(" ",40)),80)," ",)
    for Flexible up to 40 digit

  4. #4
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,163

    Re: Formula Remove slashes, insert X in front of first character and remove last to charac

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,129

    Re: Formula Remove slashes, insert X in front of first character and remove last to charac

    Thanks for the help guys. Formula works perfectly

  6. #6
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    531

    Re: Formula Remove slashes, insert X in front of first character and remove last to charac

    =SUBSTITUTE("x"&TRIM(LEFT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),2*LEN(A1)))," ",)

  7. #7
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,129

    Re: Formula Remove slashes, insert X in front of first character and remove last to charac

    Thanks for the help Ghozi

  8. #8
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,577

    Re: Formula Remove slashes, insert X in front of first character and remove last to charac

    If your data always starts with 4 digits followed by a slash followed by 6 digits as your example seems to suggest, then you can use this simpler formula...

    ="X"&LEFT(A1,4)&MID(A1,6,6)

  9. #9
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,129

    Re: Formula Remove slashes, insert X in front of first character and remove last to charac

    Thanks for your formula Rick. The data will always start with 4 digits followed by a slash followed by 6 digits

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to remove text between to character (including those characters)
    By Hecky83150 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 03-13-2020, 12:41 PM
  2. [SOLVED] Formula to remove first letter with dot in front of it from names
    By bjnockle in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-01-2020, 01:51 PM
  3. [SOLVED] Create a formula to remove last two characters from a cell if the 2nd character is a space
    By SellMeTv2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2019, 11:13 PM
  4. Remove and insert multiple characters within a cell with changing positions
    By Global999 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-17-2016, 01:34 AM
  5. [SOLVED] Formula to remove first two character is second character is a number.
    By herbie226 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2013, 02:59 PM
  6. Macros to remove a special character and all the characters after that....
    By Umapathy kumar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-07-2013, 01:27 AM
  7. Replies: 5
    Last Post: 08-29-2005, 11:05 PM

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