| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
Place a "" around ALL values in a cell
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 , "Phil" 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. |
|
#3
|
|||
|
|||
|
Place a "" around ALL values in a cell
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" wrote in message ... 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. |
|
#4
|
|||
|
|||
|
Place a "" around ALL values in a cell
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" wrote in message ... 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. |
|
#5
|
|||
|
|||
|
Place a "" around ALL values in a cell
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 , "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&"''" |
|
#6
|
|||
|
|||
|
Place a "" around ALL values in a cell
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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Place a "" around ALL values in a cell
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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Place a "" around ALL values in a cell
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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I break values apart that are in the same cell? | Excel Worksheet Functions | |||
| a unique cell value returns multiple cell values from another shee | Excel Worksheet Functions | |||
| VLookup to sum cell values | Excel Worksheet Functions | |||
| Toggle multiple values in single cell | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Hybrid Mode
