Subtotal comes with TOTAL at end. To get rid of that

[swati vyas]

Hi
Mississauga, ON Total
Montreal Total
Naresh Group- Vaughan Total
Ottawa Total
Parry Sound, ON Total
Quebec Total
Sarnia Total
Toronto, ON Total
USA Total


I need to have a formula which will give me in excel in next column only names of cities without the TOTAL at end. How do i do that? I tried left but since not all names of same number of alphabets cant use successfully.

the only way i could do it was manually deleting TOTAL in each line. Please help. thanks

 

[MARK858]

Just copy the column to the next column, select the column and then use find and replace. Put "Total" in the find box, leave the replace box blank and click replace all

 

[swati vyas]

Thanks so much! will help me so much!

 

[T. Valko]

Hi
Mississauga, ON Total
Montreal Total
Naresh Group- Vaughan Total
Ottawa Total
Parry Sound, ON Total
Quebec Total
Sarnia Total
Toronto, ON Total
USA Total


I need to have a formula which will give me in excel in next column only names of cities without the TOTAL at end. How do i do that? I tried left but since not all names of same number of alphabets cant use successfully.

the only way i could do it was manually deleting TOTAL in each line. Please help. thanks

Try this...

Book1
	A	B
2	Mississauga, ON Total	Mississauga, ON
3	Montreal Total	Montreal
4	Naresh Group- Vaughan Total	Naresh Group- Vaughan
5	Ottawa Total	Ottawa
6	Parry Sound, ON Total	Parry Sound, ON
7	Quebec Total	Quebec
8	Sarnia Total	Sarnia
9	Toronto, ON Total	Toronto, ON
10	USA Total	USA
Activity

This formula entered in B2 and copied down:

=LEFT(A2,LEN(A2)-6)

 

[snoopyhr]

Welcome to the board citizen

Here are few alternatives:

Excel Workbook
	A	B	C	D
1	Original	Option 1	Option 2	Option 3
2	Mississauga, ON Total	Mississauga, ON	Mississauga, ON	Mississauga, ON
3	Montreal Total	Montreal	Montreal	Montreal
4	Naresh Group- Vaughan Total	Naresh Group- Vaughan	Naresh Group- Vaughan	Naresh Group- Vaughan
5	Ottawa Total	Ottawa	Ottawa	Ottawa
6	Parry Sound, ON Total	Parry Sound, ON	Parry Sound, ON	Parry Sound, ON
7	Quebec Total	Quebec	Quebec	Quebec
8	Sarnia Total	Sarnia	Sarnia	Sarnia
9	Toronto, ON Total	Toronto, ON	Toronto, ON	Toronto, ON
10	USA Total	USA	USA	USA
Sheet1

 

[T. Valko]

Welcome to the board citizen

Here are few alternatives:

Excel Workbook
	A	B	C	D
1	Original	Option 1	Option 2	Option 3
2	Mississauga, ON Total	Mississauga, ON	Mississauga, ON	Mississauga, ON
3	Montreal Total	Montreal	Montreal	Montreal
4	Naresh Group- Vaughan Total	Naresh Group- Vaughan	Naresh Group- Vaughan	Naresh Group- Vaughan
5	Ottawa Total	Ottawa	Ottawa	Ottawa
6	Parry Sound, ON Total	Parry Sound, ON	Parry Sound, ON	Parry Sound, ON
7	Quebec Total	Quebec	Quebec	Quebec
8	Sarnia Total	Sarnia	Sarnia	Sarnia
9	Toronto, ON Total	Toronto, ON	Toronto, ON	Toronto, ON
10	USA Total	USA	USA	USA
Sheet1

Each of those formulas returns the space character before the word total.

=CODE(RIGHT(B2)) = 32
=CODE(RIGHT(C2)) = 32
=CODE(RIGHT(D2)) = 32

 

[MrVillareal]

Hi friend

However this just edit the original. Hope to add some help.

You can press F5---->>special--->>visible cells only
Then, = --->>arrow up---->>control enter

Hope for your feedback