How to remove first, last or certain characters from text in Excel?

We may use Excel functions to remove characters from a text string in Excel. This article is going to talk about the solutions to remove certain characters from a text string.

Remove first or last characters by formula in Excel

Remove first or last characters by Find and Replace in Excel

Remove first, last, or certain characters with Kutools for Excel

Supposing you have a range of text strings, and now you need to remove first, last or certain several characters from the text strings, as the following screenshots show:

                                                                                            

  doc-remove-certain-characters-6 

      Remove First Characters               Remove Last Characters              Remove Certain Characters


arrow blue right bubble Remove first or last characters by formula in Excel


With the Excel LEFT and RIGHT function, you can remove the certain characters from the beginning or the end of the strings. Please do as the following steps:

Remove the first four characters from the text string.

Step 1. Type the following formula in adjacent cell B1: =RIGHT(A1, LEN(A1)-4), see screenshot:

Tips: This formula means to return the right most number of characters, you need to subtract 4 characters from left string. And you can specify the number of characters you want to remove from the left string by changing the Number 4 in the formula =RIGHT(A1, LEN(A1)-4).

Step 2. Then press Enter key, and select the cell B1, then drag the fill handle over the cells that you want to contain this formula. And now you are successful in removing the first 4 characters of the text strings. See screenshot:

If you need to remove the last several characters, you can use the LEFT function as the same as the RIGHT function.

Note: Using the Excel function to remove certain characters is not as directly as it is. Just take a look at the way provided in next method, which is no more than two or three mouse clicks.


arrow blue right bubble Remove first or last characters by Find and Replace in Excel

If you want to remove all characters at the front or end of the colon :, Find and Replace function in Excel also can make your removing as soon as quickly.

Step 1. Hold the Ctrl button and press F to open Find and Replace dialog, and click Replace.

Step 2. Enter :* into the Find what box, and leave blank in Replace with box. See screenshot:

Step 3. Click Replace All, and all the characters at the end of the colon (include the colon) have been removed. See screenshot:

doc-remove-certain-characters-12

If you want to remove all characters before the colon, please type *: into the Find what box, and leave blank in Replace with box. See screenshot:

Click Replace All, all the characters before the colon have been removed. See screenshot:

Note: This method is only applied to the characters which contain the specific separators, so you can change the colon: to any other separators as your need.


arrow blue right bubble Remove first, last, or certain characters with Kutools for Excel

With the Remove by Position utility of the third party add-in Kutools for Excel, you can be easy to remove first, last or certain characters from the text string in one click.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Read More    Free Download Now

After installing Kutools for Excel, apply Remove by Position according to these steps:

1. Select the range that you want to remove the certain characters. Then click Kutools > Text > Remove by Position. See screenshot:

doc remove certain character15

2. Specify the following operations in the pop-up Remove by Position dialog box. See screenshot:

  1. Specify the number of characters to be deleted.
  2. Choose the positions where the characters started to be deleted. You can determine the position from left or right, or from a specified position. Here we remove characters from the specified position. See screenshot:

doc remove certain character16

3. The Preview pane in the right part of the box can help to see the removing result immediately. Then click OK or Apply. The specified characters have been removed from the text strings in the original range. See screenshots:
doc remove certain character17

Note: To avoid non-text cell, please choose Skip non-text cell.

Please click here to know more about this feature.            Free Download Kutools for Excel Now

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +12 prasad
its use full to make data much easier

Thanks
2014-03-21 11:01 Reply Reply with quote Quote
Permalink +1 Aleksandar
Sorry, but doesn't work for me. Shows me an error.
2016-07-12 12:22 Reply Reply with quote Quote
Permalink -2 Steve Jadrnak
Print this item to help with removing characters.
2014-06-13 12:38 Reply Reply with quote Quote
Permalink +3 fwfceas
thank u dear for this help
2014-07-11 12:16 Reply Reply with quote Quote
Permalink +2 Holly
Thank you for posting this!! :-)
2014-07-14 19:11 Reply Reply with quote Quote
Permalink +1 Happy
this is what i am looking for..
I need one more thing:
I want to copy the values which are in brackets into another cell, in your case- Postcode (ZIP) : 211230, want to copy ZIP into another cell.
2014-07-25 07:32 Reply Reply with quote Quote
Permalink +4 Wisdom Singer
Quoting Happy:
this is what i am looking for..
I need one more thing:
I want to copy the values which are in brackets into another cell, in your case- Postcode (ZIP) : 211230, want to copy ZIP into another cell.

Hi, Happy. Did you ever get an answer? If not, easiest way would be with the Mid command. For example, if Postcode (ZIP): 211230 is in Cell A1, the command would be =MID(A1,11,3) - i.e., take the middle 3 characters starting with the 11th one from the left.
2014-10-23 19:31 Reply Reply with quote Quote
Permalink +4 ZEDD
I have 275 names but in a repeated manner. I want to extract each name from that list which will appear only once.
2015-06-15 08:45 Reply Reply with quote Quote
Permalink 0 Uma Kanth
Hi Zedd,

For the task mentioned by you. We can use IF and COUNT IF combination Ex: =IF(COUNTIF($A$ 1:$A$275,A1)= 1,A1,FALSE)

Regards,
Umakanth Ramineedi
2016-08-27 09:15 Reply Reply with quote Quote
Permalink 0 ankit
select the specific column and in "data" panel.. click "remove duplicates"
2017-05-24 07:43 Reply Reply with quote Quote
Permalink +2 Rahul SIngh
Thaks for helping me
2014-07-30 04:46 Reply Reply with quote Quote
Permalink 0 Nishant Shastry
Excellent tip! You guys just made my life so much easier :)
2014-09-01 09:31 Reply Reply with quote Quote
Permalink +2 Swapnil
THanks a lot was really helpful!
2014-10-17 14:43 Reply Reply with quote Quote
Permalink 0 spaha
Great tip! Very helpfull for beginners like me! Thanks!
2014-10-30 11:29 Reply Reply with quote Quote
Permalink 0 Abdul Gani
Thank you team .... really helpful
2014-11-08 06:49 Reply Reply with quote Quote
Permalink +1 kn
I have a group of numbers. Some of them have a zero at the beginning. I can't have a zero as the first number. The numbers are not all the same length but I just want the zero gone. Is there a way to do that.
2014-11-21 15:25 Reply Reply with quote Quote
Permalink +3 Xavier
@kn :

you could use the following, considering the source cell is A1
In the formula below, if will evaluate what the first character is, and if it's a zero, it will remove it, if it is not a zero, it will remain as is.

=IF((LEFT(A1,1)="0"),RIGHT(A1,(LEN(A1)-1)),A1)

hope this helps, cheers!
2015-02-17 17:32 Reply Reply with quote Quote
Permalink 0 Ray
Hi! Hope you can help me guys how to remove any characters after first comma from Left and add
2015-02-20 01:58 Reply Reply with quote Quote
Permalink +3 Xavier
Hi #Ray,

Your question seems like it's missing some details at the end, but to remove anything after the first coma, you can use the following:

assuming your source data is in A1.
Basically, grabs everything left of the first coma it finds in the string (position of the coma minus 1)

=LEFT(A1,(SEARCH(",",A1)-1))

if A1 is abcdef,ghijkl then you will get abcdef
2015-02-27 13:20 Reply Reply with quote Quote
Permalink 0 katrina_rose
Hi guys,

set up a list with 3 columns and about 500 rows, and in every cell I have a"#" that has to be removed.
I really dont understand how to remove it and dont get on with the formula on top.
May it be because I'm using a mac device?
2015-03-20 16:06 Reply Reply with quote Quote
Permalink +4 Xavier
just do a find and replace.
Find "#" replace with "" blank.
That way you don't even need a formula.This is considering there is only one "#" you'd like to remove in each cell.

cheers!
2015-03-29 02:15 Reply Reply with quote Quote
Permalink +2 karen
but what if the characters in a cell have different font colors and I want to retain their colors?
2015-04-05 17:07 Reply Reply with quote Quote
Permalink +3 Adwait Pande
Use the format painter in that case.

Suppose your original data is in 'column A' (which is formatted with color)
and the data after applying formula is in 'column B' (unformatted)

then follow the following steps:
1) Click on 'Format Painter' from 'Home' menu
2) Click on column name 'A' (complete column should be selected)
3) Click on column name 'B'

You will have same formatting for column B as your source column (column A).

Hope this helps.
2015-04-22 10:07 Reply Reply with quote Quote
Permalink 0 Surendar
Nice tip guys. Very useful tips
2015-05-13 03:02 Reply Reply with quote Quote
Permalink 0 Lori W
Thank you! The formula works for my needs! :-)
2015-05-18 13:04 Reply Reply with quote Quote
Permalink 0 Craig
thanks, this is a great post.

I am trying to pull the first letter of two names to automate into a action log? Can you help me with this formula? Sure it is LEN. But can't just get it....

eg.

Brian Adams = BA

Thanks

Craig
2015-05-20 09:18 Reply Reply with quote Quote
Permalink +2 Xavier
@Craig, you could do this :
=CONCATENATE((MID(A2,1,1)),(MID(A2,(FIND(" ",A2)+1),1)))

considering your source data is in cell A2.
However this will not work if you have more than 2 distinct names separated by spaces. You could elaborate that in a IF statement to tackle cases where you have 3 names and want to extract 3 letters (i.e. John Bon Jovi....JBJ)

cheers!
2015-06-09 13:10 Reply Reply with quote Quote
Permalink +2 aries
try this bro A1=Brian Adams B2=CONCATENATE( LEFT(A1,1),LEFT (RIGHT(A1,SEARC H(" ",A1)-1),1))
2015-06-29 09:07 Reply Reply with quote Quote
Permalink +2 Gabriela
Awesome! This was very helpful, thanks!!!
2015-06-01 18:25 Reply Reply with quote Quote
Permalink +2 Abdul Gani
really helpful Thank you for posting this
2015-06-04 07:07 Reply Reply with quote Quote
Permalink +2 JITENDRA RAGHUVANSHI
the futnction was very helpful and because of this i could save my lagr amount of time
2015-06-04 11:12 Reply Reply with quote Quote
Permalink +2 Anoop
Hey Guys,

I need to extract only "Last, First" from a column containing values in "Last, First" as well as "Last, First MI"

What's the best function to extract it. Logically, I think I need to look for the second occurrence of " " (space) in a string (say in cell A1) and return everything left of it.

I am not able to come up with a functional arrangement to do it.
Thanks in advance for help.
2015-06-20 21:55 Reply Reply with quote Quote
Permalink +1 Anoop
I tried following formula but ran into another issue:

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+2))

However, whenever a last name had a " " in it, this formula returned an erroneous value. (ie., "De La Vega, Guzman T" returned only "De La")

So, I am thinking of a different logic.
I need to check if the second last character of the string is " " then return the string before it, otherwise, keep the string as is.

Can someone help me with an excel formula for this logic?
2015-06-20 22:47 Reply Reply with quote Quote
Permalink 0 tracey
Trying to remove * at beginning and end of a text string for example *XX-XXXX-XX-XX* and *XX-XXX-XX-XX* not getting any of these formulas to work keep getting circular error :(
2015-08-25 16:29 Reply Reply with quote Quote
Permalink 0 Dave
Quoting tracey:
Trying to remove * at beginning and end of a text string for example *XX-XXXX-XX-XX* and *XX-XXX-XX-XX* not getting any of these formulas to work keep getting circular error :(

try this;

=MID(A2,SEARCH("~*",A2,1)+1,SEARCH("~*",A2,SEARCH("~*",A2,1)+1)-2)
Assuming your data is in A2 and there are only 2 *'s in your cell.
This will look for the first *, then look for the second *, and pull everything in between.
2015-10-16 11:54 Reply Reply with quote Quote
Permalink 0 ApoAz
How can I insert a function that parses out the ".RCPRO999.com" ,".PVC999.com", and ".RVCA0887.com" and just keeps the original email. When I insert =LEFT(P4, LEN(P4)-13) it only works for for the ".RCPRO99.com" but the ending are various sizes





Thanks
M
2015-08-26 18:46 Reply Reply with quote Quote
Permalink 0 Dave
Quoting ApoAz:
How can I insert a function that parses out the ".RCPRO999.com",".PVC999.com", and ".RVCA0887.com" and just keeps the original email. When I insert =LEFT(P4, LEN(P4)-13) it only works for for the ".RCPRO99.com" but the ending are various sizes





Thanks
M


This ones a bit hazy. it all depends if your email addresses will always end with ".com" (never .co.uk or .net etc)
assuming they will always end in .com, try this;

=LEFT(A2,SEARCH(".com",A2,1)+3) Assuming your data is in cell A2.
2015-10-16 11:48 Reply Reply with quote Quote
Permalink +1 Tracy Clark
Hi,

I want to remove everything before ; from the below text, but the formula =RIGHT(A1,(SEAR CH(";",A1)-1)) doesn't work. I just want to keep XBR65X850B.

X;0;0;2;0;0;XBR65X850B

Can anyone please help?
2015-09-16 22:47 Reply Reply with quote Quote
Permalink +1 xavier
you are on the right track! some answers in the link below. you need to find the position of the last ";" in your string. Then doright(Len - that position of the last ";")
http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba
2015-09-21 02:23 Reply Reply with quote Quote
Permalink +1 GIRARD
COLUMN A1 IS P11-L22-333
COLUMN B1 IS 1122333

Please help. if A1 IS P11-L22-333. What formula will i do to make it 1122333.

(P), (-) and (L) should be erase.
2015-09-28 07:20 Reply Reply with quote Quote
Permalink +1 girard
pls help guys. i need the formula will i use
2016-01-20 05:29 Reply Reply with quote Quote
Permalink +1 katie
what should I do if I have text like
abc?1001, abc?a10001 and so on. And I want to remove part after "?" and I can't predict how long will be that part
2015-10-01 08:56 Reply Reply with quote Quote
Permalink +1 Dave
Quoting katie:
what should I do if I have text like
abc?1001, abc?a10001 and so on. And I want to remove part after "?" and I can't predict how long will be that part

=MID(A2,1,SEARC H("~?",A2,1)-1) assuming your data is in cell A2.
This will give you everything from before the question mark.
or
=MID(A2,SEARCH("~?",A2,1)+1,LEN(A2))
This will give you everything after the question mark.
When searching for "Special characters" you need to include the tilde (~) before the character.
2015-10-16 11:42 Reply Reply with quote Quote
Permalink +1 Rajesh Patel
Thanks a ton for sharing this most important formulas !
2015-10-10 22:41 Reply Reply with quote Quote
Permalink +1 Slamat Ale
Just awesome :) 1st time know *: or :* trick thax..
2015-10-14 07:41 Reply Reply with quote Quote
Permalink +1 Azim
What should I do if I want to remove every thing from the following string except NAME

9/13/15 11:08 AM: John Doe : Welcome to xyz

I have 31K entries and I am trying to clean data to capture names only
2015-10-24 20:56 Reply Reply with quote Quote
Permalink +1 Keith
I would do this in two steps just with standard find and replace (search):

1) find "*: " and replace with ""
2) find " :*" and replace with ""
2015-11-06 23:55 Reply Reply with quote Quote
Permalink +1 Robin
I'm finding for it difficult to extract *@airspan.com the following for the content "*@.com"
2015-11-18 22:03 Reply Reply with quote Quote
Permalink +1 Becky
What if I want to remove characters from both the left and the right?

I'm using a barcode scanner and I want an excel formula to remove the first 7 characters from the left and the last 2 characters on the right.

Here's what the original information looks like:

+$$8017CAPHLESS-001A-
+$$8017PRYOMCP-084QY
+$$8017LM00009CR

Here's what I want them to look like:

CAPHLESS-001
PRYOMCP-084
LM00009

Any help would be appreciated. :lol:

Thank you!
2015-12-03 22:19 Reply Reply with quote Quote
Permalink 0 Ken Adams
=RIGHT(B1,LEN(B 1)-7)
2015-12-10 22:08 Reply Reply with quote Quote
Permalink 0 Dave
Quoting Becky:
What if I want to remove characters from both the left and the right?

I'm using a barcode scanner and I want an excel formula to remove the first 7 characters from the left and the last 2 characters on the right.

Here's what the original information looks like:

+$$8017CAPHLESS-001A-
+$$8017PRYOMCP-084QY
+$$8017LM00009CR

Here's what I want them to look like:

CAPHLESS-001
PRYOMCP-084
LM00009

Any help would be appreciated. :lol:

Thank you!



assuming its always the first 7 characters and the last two that you want to leave out, this will do the trick.
=MID(A2,8,(LEN(A2)-9))
Takes the text from the middle of the string starting at character 8, then to determine where it stops the copy it subtracts 9 (7 + 2, total number of characters that you want left out) from the total number of characters in the string.

Hope this helps.
2016-02-05 11:47 Reply Reply with quote Quote
Permalink 0 Roopesh.R
:-) Thanks for the help
2015-12-08 11:55 Reply Reply with quote Quote
Permalink +1 Richie
Great info on this site as usual - I'm looking for something similar, maybe just not asking my question in the right way.

I have a combination of the following and want to remove the numbers from each step (4.[b][]),(5.[b][]) etc then (1)[]).
Basically, I want the bottom 6 steps to look like the top 3

Thanks in advance

VERIFY AND CHECK
DRAIN LUBRICANT
REFILL WITH FRESH
4. CHECK
5. COMPLETE THE ATTACHED
6. ENSURE EVERYTHING
1) SWITCH THE FOLLOWING
2) VISUALLY CHECK
3) SWITCH ALARM
2015-12-12 10:19 Reply Reply with quote Quote
Permalink 0 Dave
Quoting Richie:
Great info on this site as usual - I'm looking for something similar, maybe just not asking my question in the right way.

I have a combination of the following and want to remove the numbers from each step (4.[b][]),(5.[b][]) etc then (1)[]).
Basically, I want the bottom 6 steps to look like the top 3

Thanks in advance

VERIFY AND CHECK
DRAIN LUBRICANT
REFILL WITH FRESH
4. CHECK
5. COMPLETE THE ATTACHED
6. ENSURE EVERYTHING
1) SWITCH THE FOLLOWING
2) VISUALLY CHECK
3) SWITCH ALARM



=RIGHT(A2,(LEN( A2)-SEARCH(" ",A2,1)))

Assuming your step number always has a space after it.
2016-02-05 11:21 Reply Reply with quote Quote
Permalink 0 LINZEY
Hello, how would I select information in the middle of a string?
For example I have this: ADR292000600001 200178BLOSSOMDR IVE. And I'm looking to select ONLY the 15 digits after the 'ADR'.
I can't seem to find a way to do this.
Thanks
2016-01-11 15:19 Reply Reply with quote Quote
Permalink 0 Dave
Quoting LINZEY:
Hello, how would I select information in the middle of a string?
For example I have this: ADR292000600001200178BLOSSOMDRIVE. And I'm looking to select ONLY the 15 digits after the 'ADR'.
I can't seem to find a way to do this.
Thanks



=MID(A2,4,15)

This will select the text within a string in cell A3, starting at position 4 (Assuming it always starts with ADR) and selecting only the 15 characters after "ADR" (Again assuming the information you wants is always only 15 characters long. Spaces count as characters also.)
2016-02-05 11:27 Reply Reply with quote Quote
Permalink 0 Xavier Daneau
Yes, just use the MID function
ADR292000600001 200178BLOSSOMDR IVE
If your data is in A1 : start at the 4th char, return 15 char from that start position
=MID(A1,4,15)
2016-02-05 15:53 Reply Reply with quote Quote
Permalink 0 kittu
CPCB07-PV-0043-03
I have this type of entries in 1000s.
my task is to fill automatically, PV , in adjacent cell.
how can I do this?
kindly help
2016-01-20 23:33 Reply Reply with quote Quote
Permalink 0 Dave
Quoting kittu:
CPCB07-PV-0043-03
I have this type of entries in 1000s.
my task is to fill automatically, PV , in adjacent cell.
how can I do this?
kindly help


=MID(A2,(SEARCH("-",A2,1)+1),(SEARCH("-",A2,(SEARCH("-",A2,1)+1))-(SEARCH("-",A2,1)+1)))

So, this one assumes the PV is always enclosed in "-" and that it is always after the first "-".
It will look in cell A2, and then look for the first instance of "-" and start the copy from after that point. To find the number of characters to copy, it will look for the first "-" and then look for the second "-" and calculate the number of characters in between by subtracting the position of the second dash from the position of the first.
Its a lot simpler than i make it sound.
If it will always be two characters, (PV for example, you can simplify it with;
=MID(A2,(SEARCH("-",A2,1)+1),2)
which will look for the first instance of "-" and copy the two characters after it.

Hope this helps.
2016-02-05 11:38 Reply Reply with quote Quote
Permalink 0 Xavier Daneau
your question is not clear, can you give more details?
2016-02-05 15:54 Reply Reply with quote Quote
Permalink 0 Michael M.
Hi!
I have issues applying the formula for removing the first 4 characters
=RIGHT(A1, LEN(A1)-4)
When I insert it into Excel it opens an error window, saying that the formula is incorrect and marking =RIGHT(A1, LEN(A1)-4).
Why???
2016-02-09 14:21 Reply Reply with quote Quote
Permalink 0 Dave
Quoting Michael M.:
Hi!
I have issues applying the formula for removing the first 4 characters
=RIGHT(A1, LEN(A1)-4)
When I insert it into Excel it opens an error window, saying that the formula is incorrect and marking =RIGHT(A1, LEN(A1)-4).
Why???

Your code works fine for me. may be an issue with your version of excel. try putting the len(a1)-1 in brackets.
Failing that, tr;
=MID(A1,5,LEN(A1))
And, obviously, make sure you are not putting this code into cell A1.
i realise that these comments take about a month or two to get added, but i hope this helps.
2016-02-12 09:15 Reply Reply with quote Quote
Permalink 0 Aman Gupta
what will the solution
if have diffrent text in the colloum with some number whether in middle,in the beginning or in last how to remove all text in the colloum except numbers plz reply
2016-02-20 11:19 Reply Reply with quote Quote
Permalink 0 Zia Ahmed
Quoting Dave:
Quoting Michael M.:
Hi!
I have issues applying the formula for removing the first 4 characters
=RIGHT(A1, LEN(A1)-4)
When I insert it into Excel it opens an error window, saying that the formula is incorrect and marking =RIGHT(A1, LEN(A1)-4).
Why???

Your code works fine for me. may be an issue with your version of excel. try putting the len(a1)-1 in brackets.
Failing that, tr;
=MID(A1,5,LEN(A1))
And, obviously, make sure you are not putting this code into cell A1.
i realise that these comments take about a month or two to get added, but i hope this helps.

I nomally use this type of data sq/02/16/144/g/ 2/10 i want all data after last slash. what i do pls. guide
2016-02-23 11:05 Reply Reply with quote Quote
Permalink 0 Dave
Quoting Zia Ahmed:
Quoting Dave:
Quoting Michael M.:
Hi!
I have issues applying the formula for removing the first 4 characters
=RIGHT(A1, LEN(A1)-4)
When I insert it into Excel it opens an error window, saying that the formula is incorrect and marking =RIGHT(A1, LEN(A1)-4).
Why???

Your code works fine for me. may be an issue with your version of excel. try putting the len(a1)-1 in brackets.
Failing that, tr;
=MID(A1,5,LEN(A1))
And, obviously, make sure you are not putting this code into cell A1.
i realise that these comments take about a month or two to get added, but i hope this helps.

I nomally use this type of data sq/02/16/144/g/2/10 i want all data after last slash. what i do pls. guide


try;
=RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))
2016-04-03 15:31 Reply Reply with quote Quote
Permalink 0 natalie
I just want to say "thank you" for this tip; it has been EXTREMELY helpful!
2016-03-30 17:48 Reply Reply with quote Quote
Permalink 0 SAMULA
25/06/1994
27/12/1984
01/07/1991
12/02/1977
12/02/1977
09/07/1976
01/01/1974
01/01/1974
hello, from the above, how do i remove the first characters, i want to remain with the last 4 digits eg 1977, 1976,
2016-04-20 14:19 Reply Reply with quote Quote
Permalink 0 Dave
Quoting SAMULA:
25/06/1994
27/12/1984
01/07/1991
12/02/1977
12/02/1977
09/07/1976
01/01/1974
01/01/1974
hello, from the above, how do i remove the first characters, i want to remain with the last 4 digits eg 1977, 1976,


Try
=RIGHT(A1,4)
This says;
Starting from the right of cell A1, give me the last 4 characters.
2016-04-26 08:47 Reply Reply with quote Quote
Permalink 0 Aks
ABC xyz pq Replace Me(s)
output: "ABC RE"
EFG xyz pq Change This(
output: "EFG CT"

I have data similar to dummy data in above 2 lines in excel. Request your help to provide a formula that will replace the complete line with "RM" if found with text "Replace Me(s)" and "CT" if found with text "Change This("
2016-06-02 11:20 Reply Reply with quote Quote
Permalink 0 George
Hi,
I need help with product database.

I have names of products from database for example:
/cars/new/car1.jpg
/cars/car red2.jpg
/trucks/new/for sale/34533.jpg

So I need only the text after the last slash (jpg file name):
car1.jpg
car red2.jpg
34533.jpg

I can't predict how long it is part before slash / and after slash. It is different for different cells.

Any solution? Thank you so much
2016-06-09 07:03 Reply Reply with quote Quote
Permalink 0 CHRIS
I have data that contains both Chinese and English and I would like to trim the data so that only the English appears. The data is varying lengths and the Chinese characters I would liked trimmed are also varying lengths. How might I be able able to do this. The data looks like this:

待摊车辆保险Prepaid vehicle insurance fee
待摊保险费Prepaid insurance fee
原材料Raw material
2016-07-27 16:22 Reply Reply with quote Quote
Permalink 0 Indra Raj
I used this one RIGHT(B1, LEN(B1)-2)
Thanks
2016-07-29 07:25 Reply Reply with quote Quote
Permalink 0 Russell A
THANK GOD!!!! You just saved me two day's of work from Excel loosing all formatting. I'm going to champion this software for use within my group. You just got at least one new customer.
2016-08-04 20:20 Reply Reply with quote Quote
Permalink 0 Shabi
My string is like this "1400 10-07-1975"
I want to keep only the first "1400"
I tried, It worked for the first time with that search and replace function... but I'm unable to repeat it by putting "space*" in "find what" section
Please help...
2016-08-14 08:19 Reply Reply with quote Quote
Permalink 0 David
=LEFT(A1,SEARCH (" ",A1)-1) Should do the trick.
Basically saying;
I want text starting from the left of the string in A1, then i want to find the location of the first instance of a space and minus one from that. (otherwise your found string will contain the space).
Simple enough, hope this helps.
2016-09-19 12:38 Reply Reply with quote Quote
Permalink 0 Lourie
Hi! Thank you so much for this article! It has saved me so much time.
2016-08-22 16:57 Reply Reply with quote Quote
Permalink 0 Rao
Hi all
Is there any way to remove Vowels from a alpha string. say 'Connector, the result supposed to be 'cnnctor'.
2016-08-25 06:03 Reply Reply with quote Quote
Permalink 0 xavier
Hi,

The easiest way is to do a Find and replace. Select you range, do ctrl-F then find O replace with blank. This will remove all,'O's and replace with no character, which will remove them.

Do this with each vowel and you are done. No formula required
2016-08-25 12:22 Reply Reply with quote Quote
Permalink 0 Slamat
use subsitute function
2016-08-25 10:28 Reply Reply with quote Quote
Permalink 0 Sachin Sawang
Hi Team,

Need formula for removing first character from cell if it is special only.

Kindly help...
2016-08-28 06:41 Reply Reply with quote Quote
Permalink 0 SARPK
I want to delete the left 4 digit in whole sheet...

for example 5895012032251.. ......the result should be 012032251
I can do the one only but how to do in whole worksheet..
2016-09-09 07:27 Reply Reply with quote Quote
Permalink 0 Ramin Ramesh
hey tanx dears
Replace All worked for me. hhhh very good trick..
2016-09-11 07:53 Reply Reply with quote Quote
Permalink 0 narender
send the kutools for xp
2016-11-11 05:13 Reply Reply with quote Quote
Permalink 0 N-tone
Thanks you for a very simple and a comprehensible approach. It works!!!! God bless you my dear Fellow-Anonymous!
It took me 2 days of painful searches through Russian web and quite a number brain cells to find Excel Functions in Russian with explanatory notes provided, but all was in vain just because formulas refuse to work!! It seems like the accessibility and the total unwillingless to share common practices among my Russian colleagues, who just pretend they know *uck all about Microsoft products is still a big issue!!
No, seriously, I owe u!
Thanks!
2016-12-27 05:34 Reply Reply with quote Quote
Permalink 0 YODA
how can I mass delete a * in a excel spreadsheet description line?
2017-01-11 16:11 Reply Reply with quote Quote
Permalink 0 Xavier
Hi Yoda,

just do a find and replace. You can easily acces the function using CTRL+F.
You will want to look for "*" and replace with ""
basically, wherever excel finds * it will replace it with a blank.
2017-01-13 14:58 Reply Reply with quote Quote
Permalink 0 Carlos
Hi.
I would like to remove the last characters by using find and replace, but with Excel's Visual Basic (with commands).
I would like to create a file that loads an existing file and creates a new one, with the column edited.

Any help will be appreciated.

Regards,
2017-01-16 16:13 Reply Reply with quote Quote
Permalink 0 Maria
Hi!

I Would like to removed selected characters on my excel workbook, how will i do that? is there any formulas for it?

your immediate advice is highly appreciated.

Thanks & BR,
2017-01-22 09:26 Reply Reply with quote Quote
Permalink 0 Saif
I have data on browsers used by users where in I need to separate version type and names.
Formula used by me is =LEFT(F7(MIN(FI ND({0,1,2,3,4,5 ,6,7,8,9},F7&"0 123456789")))-1 ). But its not working for all inputs. Data available is List of browser versions. Output needed is Group the browser versions. For example, Google Chrome 9.0, Google Chrome 9.1 and Google Chrome 9.3 should be grouped under “Google Chrome”. Version => Type: Google Chrome 9.1 => Google Chrome. Google Chrome 9.0 => Google Chrome. Google Chrome 8.0 => Google Chrome. Google Chrome 7.0 => Google Chrome. Google Chrome 6.0 => Google Chrome. Conditions are: Do not pivot the table. Need two columns – Version and Type. You can split the column but the catch is, few versions have numbers in the beginning. Eg: 360 Secure Browser
2017-03-03 09:50 Reply Reply with quote Quote
Permalink 0 Praveen Issac Shaji
Hi,

I need support from Kutools team as i believe only you guys can help me. I have purchased a kutools license and couldn't find this feature in it.

I would like to password protect/unprote ct a 'sheet1' of 300 workbooks in one folder.
For example: Only certain cells are editable in the password protected sheet1. You need to unprotect it with a password to edit all the cells.

Scenario : When I open a workbook sheet1 opens and only certain cells are editable.(This is for on fields workers)
For office purpose all the cells have to be editable and this is protected with a password which on field workers don't know.

After some editing we have to protect back the sheet and save it.
This happens for 300 workbooks' sheet1.
So everytime

Opening workbook -> Review->Unprote ct Sheet->Enter Password.
After Changes
Review->Protect sheet->Enter Password->Save


Can we do the above steps for a folder of workbooks using a VBA or any such help would be much appreciated.
2017-03-11 07:36 Reply Reply with quote Quote
Permalink 0 HELPY
Hi I want to remove everything after the first dash(-) and keep only the letters.
How do I do that?
example 123-ABC-BDER-AK D
2017-03-21 13:20 Reply Reply with quote Quote
Permalink 0 Manish Gupta
Dear Member,
How do we find
Data Required
201 205
2102 2105
203 205
2015 2015
207 210
2147 2150
1000 1000
2017-05-02 11:05 Reply Reply with quote Quote
Permalink 0 thilip
Please tell me how to get particular data in a cell
Example
Column A
Ip-bs-bedroom
Gh-ff-kitchen
Ip-gh-main door
.
.
Now I want in column B has only location ,that's bedroom, kitchen so I want to remove other except location
2017-05-23 13:37 Reply Reply with quote Quote
Permalink 0 Pratik
You can use Find and Replace (Ctr + H). Find what: "*-" and Replace with: keep this field blank. Then click replace.
2017-05-24 12:39 Reply Reply with quote Quote

Add comment


Security code
Refresh