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
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
Last edited by manian; 02-11-2013 at 08:25 AM.
If your data is in cell A2 then something like this:
=LEFT(A2,FIND("~",SUBSTITUTE(A2,".","~",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1)
Hai
Thanks a lot it works. Can you explain me about the formula
Regards
Mani
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"
Hai
Thanks for your detail Explanation .
Regards
Mani
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks