+ Reply to Thread
Results 1 to 5 of 5

Formula to remove Character

  1. #1
    Registered User
    Join Date
    07-27-2010
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    15

    Formula to remove Character

    Hai All

    I have attached a excel in which i need a formula in Column (D2 to D11) that will remove the character of the Child Id (Column B2 to B1) & return the Value as shown in Parent Id (Column C2 to C11)

    Thanks

    Regards
    Mani
    Attached Files Attached Files
    Last edited by manian; 02-11-2013 at 08:25 AM.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: How to remove Character

    If your data is in cell A2 then something like this:

    =LEFT(A2,FIND("~",SUBSTITUTE(A2,".","~",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1)

  3. #3
    Registered User
    Join Date
    07-27-2010
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: How to remove Character

    Hai

    Thanks a lot it works. Can you explain me about the formula

    Regards
    Mani

  4. #4
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Formula to remove Character

    Sure:

    SUBSTITUTE replaces certain characters in a string with other characters.

    If, for example, we had the word "Hello" in cell A1 then

    =SUBSTITUTE(A1,"l","m")

    Would return then string "Hemmo"

    We can also specify which occurrence of the letter we want changed. The formula:

    =SUBSTITUTE(A1,"l","m",2)

    Would return "Helmo" (only the 2nd 'l' would be replaced with an 'm')

    SUBSTITUTE is used twice in my formula. The 2nd time is to count how many periods appear in the original string, because:

    =LEN(A2)

    Returns the length (number of characters) in the string in cell A1, while:

    =LEN(SUBSTITUTE(A1,".","")

    Returns the length of the string if all of the periods were removed, so taking the latter away from the former tells you how many periods there are in the string.

    Returning to our "Hello" example:

    =LEN("Hello") - LEN(SUBSTITUTE("Hello","l",""))

    Would return the length of "Hello" (5 characters) minus the length of "Heo" (3 characters, the l's having been removed) to give an answer of 2.

    That's nested inside another substitute, this time replacing a period with a tilde (~), but only the last period. Hence "ABC.1.2" would be identified as having two periods and then the 2nd period would be substituted for a tilde, leaving the string "ABC.1~2"

    The FIND statement then finds the position of the ~ in the string (in the example above the 6th character) and returns the portion of the original string to the left of that, hence "ABC.1"

  5. #5
    Registered User
    Join Date
    07-27-2010
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Formula to remove Character

    Hai
    Thanks for your detail Explanation .

    Regards
    Mani

+ 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