Removing Unneeded Characters from Cells

[carrieebacon]

For some reason when I import csv files that have been exported from Civil 3D they contain characters that are not needed. For example, the letter P is in front of the text in each cell or by itself in a cell. In column D, each cell contains a structure ID such as CI #145. Instead, it says C4;CI #145}. Is there any way for me to do a mass clean up to get rid of what I don't need?

 

[dreid1011]

Simplest way would be to select the entire sheet and use Ctrl+H to find and replace each unwanted character with no space "". Though you would have to make sure there aren't any valid combinations of unwanted characters that you want to keep.

How many different unwanted characters are there? More than the sample you have provided?

 

[carrieebacon]

Simplest way would be to select the entire sheet and use Ctrl+H to find and replace each unwanted character with no space "". Though you would have to make sure there aren't any valid combinations of unwanted characters that you want to keep.

How many different unwanted characters are there? More than the sample you have provided?

I believe only 6 unwanted characters.

 

[dreid1011]

I believe only 6 unwanted characters.

How many sheets are you dealing with? Is the scope of this task broader than using CTRL+H each time?
And what are all the unwanted characters?

 

[Joe4]

If for a particular column, the unwanted characters are always in the same place (i.e. the letter "P" is in the first spot in every cell in a particular column), you may be able to use Text to Columns in some cases to easily split off and discard the first character. Just highlight the column, go to Text to Columns (on the Data menu), selected "Fixed width", put the delimiter after the first character and then set the first field to "Do not import column (skip)", and it will lop that leading character off every cell in the column all at once.

 

[carrieebacon]

This is what I am working with after using Text to Columns. When I import the csv, everything is together in 1 column, separated by backslashes.

 

[Joe4]

What does the original CSV file look like, when viewed in a Text editor (like NotePad), before bringing it into Excel?
Can you post an image of that?

 

[carrieebacon]

Here it is

 

[Joe4]

OK, that does not appear to actually be a CSV at all. CSV stands for "Comma Separated Value", meaning commas are used to separate the different data fields.
Some places will use semi-colon instead of commas as separators.
But I do not see that at all. It does not look like you are using commas or semi-colons as field separators.

Based on this sample data you have posted, can you show us exactly what you want to get out of it?
What do you want the end product to look like?
You can show us an example based on your data above.

 

[carrieebacon]

This is the file in my folder (selected)

This is what I would like the end product to look like: