automation to remove certain characters from a cell

  • Thread starter Thread starter srosetti
  • Start date Start date
S

srosetti

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
 
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.
 
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
 
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
 
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.
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

Tried the above formulas.. They worked great. Just had to edit the +
or - values for the two different columns, but it worked great..


Thanks :-)
 
Back
Top