# Formula to remove Character

1. ## 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

2. ## 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. ## Re: How to remove Character

Hai

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

Regards
Mani

4. ## 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. ## Re: Formula to remove Character

Hai
Thanks for your detail Explanation .

Regards
Mani

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

#### Posting Permissions

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

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.05819 seconds
• Memory Usage 8,928KB
• Queries Executed 15 (?)
Template Usage (32):
• (1)footer
• (1)forumrules
• (1)gobutton
• (5)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (5)postbit_legacy
• (5)postbit_onlinestatus
• (5)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (47):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete
• forumrules