Place a "" around ALL values in a cell

Hello,

I have a situation where I have been told the only way to get my data to
work with Access is to take EVERY value (some 2,000) in each cell, and wrap a
double quote around ANY of the values that reside in the cell.

So the value 54712332 would need to look like "54712332".

How do I go about doing that?

TIA for your replies!

Phil.

If you just want to export the file with quotes, see

http://www.mcgimpsey.com/excel/textf...#csvwithquotes

If you actually want to convert all your values and formulae to
quote-wrapped values:

Public Sub QuoteWrap()
Const q = """" 'double the double quotes
Dim ws As Worksheet
Dim rCell As Range
For Each ws In ActiveWorkbook.Worksheets
For Each rCell In ws.UsedRange
If Not IsEmpty(rCell) Then _
rCell.Value = q & rCell.Value & q
Next rCell
Next ws
End Sub


In article <E0D2A471-C291-44BF-A900-AAF29A7A9E0A@microsoft.com>,
"Phil" <Phil@discussions.microsoft.com> wrote:

> Hello,
>
> I have a situation where I have been told the only way to get my data to
> work with Access is to take EVERY value (some 2,000) in each cell, and wrap a
> double quote around ANY of the values that reside in the cell.
>
> So the value 54712332 would need to look like "54712332".
>
> How do I go about doing that?
>
> TIA for your replies!
>
> Phil.

Not really sure about this but you could try, if you're data is in Column
A1:A2000
In B1
=""""&A1&""""
Copy down from B1:B2000 and Copy> Paste Special > Values to lose the
formulas,
See if it works!
Regards,
Alan.
"Phil" <Phil@discussions.microsoft.com> wrote in message
news:E0D2A471-C291-44BF-A900-AAF29A7A9E0A@microsoft.com...
> Hello,
>
> I have a situation where I have been told the only way to get my data to
> work with Access is to take EVERY value (some 2,000) in each cell, and
> wrap a
> double quote around ANY of the values that reside in the cell.
>
> So the value 54712332 would need to look like "54712332".
>
> How do I go about doing that?
>
> TIA for your replies!
>
> Phil.

The =""""&a1&"""" doesn't work because it recognizes the entire string as
between quotes and results in a1 surrounded by the &, instead of the value in
a1 surrounded by quotes. The following works for me:
="''"&B5&"''"
This may look similar but let me write it in text so you can see how it's
different -shown entered as keystrokes(separated by commas)...

equals,double quote,single quote,single quote,double quote,&,B,5,&,double
quote,single quote,single quote,double quote

So it's putting two single quotation marks which results in a double to fool
excel.

hth,
Dave


"Alan" wrote:

> Not really sure about this but you could try, if you're data is in Column
> A1:A2000
> In B1
> =""""&A1&""""
> Copy down from B1:B2000 and Copy> Paste Special > Values to lose the
> formulas,
> See if it works!
> Regards,
> Alan.
> "Phil" <Phil@discussions.microsoft.com> wrote in message
> news:E0D2A471-C291-44BF-A900-AAF29A7A9E0A@microsoft.com...
> > Hello,
> >
> > I have a situation where I have been told the only way to get my data to
> > work with Access is to take EVERY value (some 2,000) in each cell, and
> > wrap a
> > double quote around ANY of the values that reside in the cell.
> >
> > So the value 54712332 would need to look like "54712332".
> >
> > How do I go about doing that?
> >
> > TIA for your replies!
> >
> > Phil.
>
>
>

Did you actually try it? For me:

A1: hello
A2: =""""&a1&"""" ===> "hello"

The OP wanted double quotes around the contents, not single quotes.

In article <7204B09D-22F6-4792-A2E0-010A1F93B8D2@microsoft.com>,
"Dave Breitenbach" <DaveBreitenbach@discussions.microsoft.com> wrote:

> The =""""&a1&"""" doesn't work because it recognizes the entire string as
> between quotes and results in a1 surrounded by the &, instead of the value in
> a1 surrounded by quotes. The following works for me:
> ="''"&B5&"''"

You sure that doesn't work?

It worked fine for me.

Dave Breitenbach wrote:
>
> The =""""&a1&"""" doesn't work because it recognizes the entire string as
> between quotes and results in a1 surrounded by the &, instead of the value in
> a1 surrounded by quotes. The following works for me:
> ="''"&B5&"''"
> This may look similar but let me write it in text so you can see how it's
> different -shown entered as keystrokes(separated by commas)...
>
> equals,double quote,single quote,single quote,double quote,&,B,5,&,double
> quote,single quote,single quote,double quote
>
> So it's putting two single quotation marks which results in a double to fool
> excel.
>
> hth,
> Dave
>
> "Alan" wrote:
>
> > Not really sure about this but you could try, if you're data is in Column
> > A1:A2000
> > In B1
> > =""""&A1&""""
> > Copy down from B1:B2000 and Copy> Paste Special > Values to lose the
> > formulas,
> > See if it works!
> > Regards,
> > Alan.
> > "Phil" <Phil@discussions.microsoft.com> wrote in message
> > news:E0D2A471-C291-44BF-A900-AAF29A7A9E0A@microsoft.com...
> > > Hello,
> > >
> > > I have a situation where I have been told the only way to get my data to
> > > work with Access is to take EVERY value (some 2,000) in each cell, and
> > > wrap a
> > > double quote around ANY of the values that reside in the cell.
> > >
> > > So the value 54712332 would need to look like "54712332".
> > >
> > > How do I go about doing that?
> > >
> > > TIA for your replies!
> > >
> > > Phil.
> >
> >
> >

--

Dave Peterson

sorry I was trying it with three quotes instead of 4. Normally you'd have
whatever text you want to add inside quotes - in this case it was a single
double quote around the number - so 3 quotes would normally do it. In this
case you need to surround the wanted text with two double quotes to get it to
work. In the traditional manner, it would place the entire string including
the & in quotes.

[just looking back on this post and figured I'd answer in case you were
curious how I got my answer]

"Dave Peterson" wrote:

> You sure that doesn't work?
>
> It worked fine for me.
>
> Dave Breitenbach wrote:
> >
> > The =""""&a1&"""" doesn't work because it recognizes the entire string as
> > between quotes and results in a1 surrounded by the &, instead of the value in
> > a1 surrounded by quotes. The following works for me:
> > ="''"&B5&"''"
> > This may look similar but let me write it in text so you can see how it's
> > different -shown entered as keystrokes(separated by commas)...
> >
> > equals,double quote,single quote,single quote,double quote,&,B,5,&,double
> > quote,single quote,single quote,double quote
> >
> > So it's putting two single quotation marks which results in a double to fool
> > excel.
> >
> > hth,
> > Dave
> >
> > "Alan" wrote:
> >
> > > Not really sure about this but you could try, if you're data is in Column
> > > A1:A2000
> > > In B1
> > > =""""&A1&""""
> > > Copy down from B1:B2000 and Copy> Paste Special > Values to lose the
> > > formulas,
> > > See if it works!
> > > Regards,
> > > Alan.
> > > "Phil" <Phil@discussions.microsoft.com> wrote in message
> > > news:E0D2A471-C291-44BF-A900-AAF29A7A9E0A@microsoft.com...
> > > > Hello,
> > > >
> > > > I have a situation where I have been told the only way to get my data to
> > > > work with Access is to take EVERY value (some 2,000) in each cell, and
> > > > wrap a
> > > > double quote around ANY of the values that reside in the cell.
> > > >
> > > > So the value 54712332 would need to look like "54712332".
> > > >
> > > > How do I go about doing that?
> > > >
> > > > TIA for your replies!
> > > >
> > > > Phil.
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

Gotcha.

Dave Breitenbach wrote:
>
> sorry I was trying it with three quotes instead of 4. Normally you'd have
> whatever text you want to add inside quotes - in this case it was a single
> double quote around the number - so 3 quotes would normally do it. In this
> case you need to surround the wanted text with two double quotes to get it to
> work. In the traditional manner, it would place the entire string including
> the & in quotes.
>
> [just looking back on this post and figured I'd answer in case you were
> curious how I got my answer]
>
> "Dave Peterson" wrote:
>
> > You sure that doesn't work?
> >
> > It worked fine for me.
> >
> > Dave Breitenbach wrote:
> > >
> > > The =""""&a1&"""" doesn't work because it recognizes the entire string as
> > > between quotes and results in a1 surrounded by the &, instead of the value in
> > > a1 surrounded by quotes. The following works for me:
> > > ="''"&B5&"''"
> > > This may look similar but let me write it in text so you can see how it's
> > > different -shown entered as keystrokes(separated by commas)...
> > >
> > > equals,double quote,single quote,single quote,double quote,&,B,5,&,double
> > > quote,single quote,single quote,double quote
> > >
> > > So it's putting two single quotation marks which results in a double to fool
> > > excel.
> > >
> > > hth,
> > > Dave
> > >
> > > "Alan" wrote:
> > >
> > > > Not really sure about this but you could try, if you're data is in Column
> > > > A1:A2000
> > > > In B1
> > > > =""""&A1&""""
> > > > Copy down from B1:B2000 and Copy> Paste Special > Values to lose the
> > > > formulas,
> > > > See if it works!
> > > > Regards,
> > > > Alan.
> > > > "Phil" <Phil@discussions.microsoft.com> wrote in message
> > > > news:E0D2A471-C291-44BF-A900-AAF29A7A9E0A@microsoft.com...
> > > > > Hello,
> > > > >
> > > > > I have a situation where I have been told the only way to get my data to
> > > > > work with Access is to take EVERY value (some 2,000) in each cell, and
> > > > > wrap a
> > > > > double quote around ANY of the values that reside in the cell.
> > > > >
> > > > > So the value 54712332 would need to look like "54712332".
> > > > >
> > > > > How do I go about doing that?
> > > > >
> > > > > TIA for your replies!
> > > > >
> > > > > Phil.
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson