Solved Remove text from many cells without using text to colums

August 12, 2013 at 09:31:21
Specs: Windows XP
I am trying to remove the semicolon followed by the "#" symbol, and 3 numbers for the end of text stream throuout the work book. e.g. "Doe, John;#123", "Doe, Jane;#345", "Doe, Ann;#678". text to columns is not a good choice because the report has many rows and it takes time to copy/paste. Is there another way to remove this text?

See More: Remove text from many cells without using text to colums

Report •

✔ Best Answer
August 19, 2013 at 15:27:21
This formula allowed me to remove the last character and the end of the string.
=LEFT(B2,LEN(B2)-1)


#1
August 12, 2013 at 10:22:28
What application are you utilizing to do this? Excel? Quattro? notepad?
Also what is the file extension? (example, excel is .xls(x))

www.standby-it.com


Report •

#2
August 12, 2013 at 11:20:03
I am using Excel with the xls extension.

Report •

#3
August 12, 2013 at 11:30:11
Excel version please? Thanks.

www.standby-it.com


Report •

Related Solutions

#4
August 12, 2013 at 11:34:12
The excel verions is 2007.

Report •

#5
August 12, 2013 at 11:36:04
The version is Excel 2007.

Report •

#6
August 12, 2013 at 11:43:27
Have you tried using Find & Replace? Just replace the portions you don't need with nothing. Worked with a sample I created (not 100% if it matches yours).

Find or replace text and numbers on a worksheet: office.microsoft.com/en-us/excel-help/find-or-replace-text-and-numbers-on-a-worksheet-HP001216390.aspx

This thread may also help (but you will need to register to get the sheet): http://www.excelforum.com/excel-gen...

www.standby-it.com


Report •

#7
August 12, 2013 at 11:46:39
I don't know how to find when the numbers keep changing. How do I tell excel to look for changing numbers? e.g. Doe, John;#123 next name is Doe, Jane;#456.

Report •

#8
August 12, 2013 at 11:53:07
If you don't need any of the numbers & symbols just replace each on the worksheet one at a time. (make a backup first!)

e.g.
Find: #
Replace:
(replace all)

Find: 0
Replace:
(replace all)
so on, and so forth.

Find: 1
Replace:
(replace all)

Should be much faster than sorting through it and pulling manually.

www.standby-it.com


Report •

#9
August 12, 2013 at 11:56:22
Is there a way to write a macro to find and replace the ";#123)" text with a "<blank>" space?

Report •

#10
August 12, 2013 at 12:09:14
The macro from the previous link in #6 should give you the ability to pull out the name and remove the rest of the data (with whatever you want).

Otherwise, when you
Find: ; (or whatever value you want to have a space instead of)
Replace: <---- (space)

Then just find and replace the other values you dont need with no space.

www.standby-it.com


Report •

#11
August 12, 2013 at 16:14:21
If all the data your trying to remove is of the form:

Semicolon Hashtag numbers

Try this:

Select Home Tab
Select Find & Select (last box on the right)
Select Replace

In the Find What box enter: ;*
That is a semi-colon followed by a star.
The star is a wild card that matches any character.

In the Replace With box, leave blank

Then, just Select All

See how that works.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#12
August 13, 2013 at 15:44:23
mmcconaghy,

This works for the cells with one name, but not for the cells with multiply names and number combinations.


Report •

#13
August 13, 2013 at 15:48:03
It would make life so much simpler if you had only one thread going.

See my answer in your other post.

MIKE

http://www.skeptic.com/


Report •

#14
August 13, 2013 at 16:26:00
Excel 2007 is what I'm using.

Report •

#15
August 19, 2013 at 15:27:21
✔ Best Answer
This formula allowed me to remove the last character and the end of the string.
=LEFT(B2,LEN(B2)-1)

Report •

Ask Question