How to remove numbering (1., 2., etc.) from cells in excel

[Legacy 362812]

Hi, I have a spreadsheet with numbering baked into each cell. For example:

[TABLE="class: outer_border, width: 300, align: left"]
<tbody>[TR]
[TD]1. Pancakes
[/TD]
[/TR]
[TR]
[TD]2. Waffles[/TD]
[/TR]
[TR]
[TD]3. French Toast[/TD]
[/TR]
[TR]
[TD]34. Peach Pie[/TD]
[/TR]
[TR]
[TD]167. Filet Mignon[/TD]
[/TR]
</tbody>[/TABLE]








There are 100 cells and I want to remove the "1. " or "167. " from each cell so that I end up with...

[TABLE="class: outer_border, width: 300"]
<tbody>[TR]
[TD]Pancakes[/TD]
[/TR]
[TR]
[TD]Waffles[/TD]
[/TR]
[TR]
[TD]French Toast[/TD]
[/TR]
[TR]
[TD]Peach Pie[/TD]
[/TR]
[TR]
[TD]Filet Mignon[/TD]
[/TR]
</tbody>[/TABLE]

How would I do it (besides the obvious "click into each cell and remove what you don't want"? I'm running Excel for Mac 2015. I'm not super-familiar with formulas nor vba scripts. Thanks in advance for your help!

 

[jtakw]

Hi,

This should do what you want.

Excel 2010
	A	B
1	1. Pancakes	Pancakes
2	2. Waffles	Waffles
3	3. French Toast	French Toast
4	34. Peach Pie	Peach Pie
5	167. Filet Mignon	Filet Mignon
Sheet1

Cell Formulas
Range	Formula
B1	=RIGHT(A1,(LEN(A1)-(SEARCH(".",A1)+1)))

B1 formula copied down.

 

[mikerickson]

Use Global Find&Replace Find ". " Replace "."
Use TextToColumns with a period delimiter. Don't import the first column.

 

[Legacy 362812]

Thanks a ton @jtakw, this worked great! Thanks also @mikerickson.

 

[jtakw]

You're welcome, welcome to the forum.

 

[Legacy 362812]

One followup question: after I've created the second column (in a first spreadsheet) how can I then copy B1:B100 into another (a second) spreadsheet without the errors? Is there a way 'bake' the results into the cell so that I can copy them as cell values instead of as the formula? I tried typing "=B1" into C1 but then when I copy that from the first spreadsheet into the second spreadsheet it just references the column to the left of the second spreadsheet instead of putting the values in from the first spreadsheet.

 

[jtakw]

Hi,

You can just copy the entire B Column or select the entire B Column range with data and copy, go to your other sheet, and paste "Values" with the 123 symbol.

 

[Legacy 362812]

Perfect. Thanks for the help! For future readers of this post note that both @jtakw's and @mikerickson's solutions work. In my case had some titles with a period in them in other places, e.g. "World's B.E.S.T Flapjacks", which Find & Replace removed, so the second column formula with paste special > values was what I used.