automation to remove certain characters from a cell
 

I have 2 column's that I wish to remove certain data from. The cells
have various lengths in data, but each column starts and ends with a
certain char I want to remove.

For instance in Column B the cell data starts and ends with a " sign.
I need these removed.

In Column C the cells all start with a " sign and end with a ");
These symbols need to be removed from the respective column data. I
have over 50,000 entries so..this is why I'm looking to automate it
with either a macro, vba, direct excel solution etc.


Any Ideas on the problem at hand?


Thanks

automation to remove certain characters from a cell
 

Show sample data or
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"srosetti" wrote in message
...
I have 2 column's that I wish to remove certain data from. The cells
have various lengths in data, but each column starts and ends with a
certain char I want to remove.

For instance in Column B the cell data starts and ends with a " sign.
I need these removed.

In Column C the cells all start with a " sign and end with a ");
These symbols need to be removed from the respective column data. I
have over 50,000 entries so..this is why I'm looking to automate it
with either a macro, vba, direct excel solution etc.


Any Ideas on the problem at hand?


Thanks

automation to remove certain characters from a cell
 

Hi srosetti

My first port of call would be to use a find and replace All. Or is
that too simplistic, is there more symbols in the column than just "
and ");

Assuming for Col C there is only three characters as a suffix then you
could use something like this and modify the formula for Col B.

Assumes data strarts in Cell C2.


=MID(C2,1,LEN(C2)-3)

Take care

Marcus

automation to remove certain characters from a cell
 

Hi

In an unused column use this formula to remove first and last letter
from C1:

=MID(C1,2,LEN(C1)-2)

Drag the formula to next column, then drag down the formula to last
data cell.

If you then want to the formula result to values, copy the formula
colunms and use Paste Special / Values.

Hopes this helps.

Per

On 13 Nov., 00:54, srosetti wrote:
I have 2 column's that I wish to remove certain data from. *The cells
have various lengths in data, but each column starts and ends with a
certain char I want to remove.

For instance in Column B the cell data starts and ends with a " sign.
I need these removed.

In Column C the cells all start with a " sign and end with a ");
These symbols need to be removed from the respective column data. *I
have over 50,000 entries so..this is why I'm looking to automate it
with either a macro, vba, direct excel solution etc.

Any Ideas on the problem at hand?

Thanks

automation to remove certain characters from a cell
 

On Nov 12, 5:08*pm, "Don Guillett" wrote:
Show sample data or
* * * If desired, send your file to my address below. I will only look if:
* * * 1. You send a copy of this message on an inserted sheet
* * * 2. You give me the newsgroup and the subject line
* * * 3. You send a clear explanation of what you want
* * * 4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"srosetti" wrote in message

...

I have 2 column's that I wish to remove certain data from. *The cells
have various lengths in data, but each column starts and ends with a
certain char I want to remove.

For instance in Column B the cell data starts and ends with a " sign.
I need these removed.

In Column C the cells all start with a " sign and end with a ");
These symbols need to be removed from the respective column data. *I
have over 50,000 entries so..this is why I'm looking to automate it
with either a macro, vba, direct excel solution etc.

Any Ideas on the problem at hand?

Thanks



Because of the nature of the file.. I will only be able to send some
sample data.
I'll give some various examples of different types of data.



Sample Data Expected Data
Column B C Column B
C
"AGR30P" "01683"); AGR30P
01683
"AM30LP-P" "03340"); AM30LP-P
03340
"Regency" "10602-70301"); Regency
10602-70301
"3500 series" "11002"); 3500 series
11002
"4040-NAT" "10602-71202"); 4040-NAT
10602-71202
"6000-S-41001" "11002"); 6000-S-41001
11002
"GF540-286-SB" "39400"); GF540-286-SB
39400
"BGA48-BQARL" "03360"); BGA48-BQARL
03360


Ok, You should get the picture of what i'm doing from the sample
data. I picked random entries throughout the spreadsheet so it was a
fair sampling. I have to do this over 50,000 times so...

There is exactly this number of rows down.. 53,077 entries or rows of
data. They all basically look like this in Column B and C.

Hope this helps you peeps with seeing what I'm asking..


Thank You

automation to remove certain characters from a cell
 

On Nov 12, 5:32*pm, srosetti wrote:
On Nov 12, 5:08*pm, "Don Guillett" wrote:



Show sample data or
* * * If desired, send your file to my address below. I will only look if:
* * * 1. You send a copy of this message on an inserted sheet
* * * 2. You give me the newsgroup and the subject line
* * * 3. You send a clear explanation of what you want
* * * 4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"srosetti" wrote in message

....

I have 2 column's that I wish to remove certain data from. *The cells
have various lengths in data, but each column starts and ends with a
certain char I want to remove.

For instance in Column B the cell data starts and ends with a " sign.
I need these removed.

In Column C the cells all start with a " sign and end with a ");
These symbols need to be removed from the respective column data. *I
have over 50,000 entries so..this is why I'm looking to automate it
with either a macro, vba, direct excel solution etc.

Any Ideas on the problem at hand?

Thanks

Because of the nature of the file.. *I will only be able to send some
sample data.
I'll give some various examples of different types of data.

* * * * *Sample Data * * * * * * * * * * * * * * *Expected Data
Column * *B * * * * * * C * * * * * * * * *Column * B
C
* * * *"AGR30P" * * * *"01683"); * * * * * * * * *AGR30P
01683
* * * *"AM30LP-P" * * *"03340"); * * * * * * * * *AM30LP-P
03340
* * * *"Regency" * * * "10602-70301"); * * * * * *Regency
10602-70301
* * * *"3500 series" * "11002"); * * * * * * * * *3500 series
11002
* * * *"4040-NAT" * * *"10602-71202"); * * * * * *4040-NAT
10602-71202
* * * *"6000-S-41001" *"11002"); * * * * * * * * *6000-S-41001
11002
* * * *"GF540-286-SB" *"39400"); * * * * * * * * *GF540-286-SB
39400
* * * *"BGA48-BQARL" * "03360"); * * * * * * * * *BGA48-BQARL
03360

Ok, You should get the picture of what i'm doing from the sample
data. *I picked random entries throughout the spreadsheet so it was a
fair sampling. *I have to do this over 50,000 times so...

There is exactly this number of rows down.. 53,077 entries or rows of
data. *They all basically look like this in Column B and C.

Hope this helps you peeps with seeing what I'm asking..

Thank You

That data didn't look so well.. Hope this helps

Sample Data Expected Data
B C Column B Column C
"AGR30P" "01683"); AGR30P 01683
"AM30LP-P" "03340"); AM30LP-P 03340
"Regency" "10602-70301"); Regency 10602-70301
"3500 series" "11002"); 3500 series 11002
"4040-NAT" "10602-71202"); 4040-NAT 10602-71202
"6000-S-41001" "11002"); 6000-S-41001 11002
"GF540-286-SB" "39400"); GF540-286-SB 39400
"BGA48-BQARL" "03360"); BGA48-BQARL 03360


Hope this looks better than previous post. One important thing to note
is....
In Column C it is perfectly ok if the zeroes in the beginning of the
whole numbers
drop off because excel decides to turn 01683 into 1683. That's fine
if it does.


Thanks again

automation to remove certain characters from a cell
 

On Nov 12, 5:43*pm, srosetti wrote:
On Nov 12, 5:32*pm, srosetti wrote:



On Nov 12, 5:08*pm, "Don Guillett" wrote:

Show sample data or
* * * If desired, send your file to my address below. I will only look if:
* * * 1. You send a copy of this message on an inserted sheet
* * * 2. You give me the newsgroup and the subject line
* * * 3. You send a clear explanation of what you want
* * * 4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"srosetti" wrote in message

....

I have 2 column's that I wish to remove certain data from. *The cells
have various lengths in data, but each column starts and ends with a
certain char I want to remove.

For instance in Column B the cell data starts and ends with a " sign.