I have a worksheet which includes a column that has anywhere from 6-14 digits. After data is entered for this project I need to copy the digits from excel to a notepad. Please see how the format should read in notepad below.
I found a way to format adding the single quote and comma but not the carriage return. Is there a simple formula for this?
EXCEL
728474
1002208755
1001873448
783715
791490
Notepad
('728474','1002208755','2018551436','1001873448','783715','791490')
Are you OK with using a VBA function to do this?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
If so, copy the code at this link:
http://www.excelforum.com/showthread...=1#post3096647
Paste it into a general module.
Then, use it on the worksheet like this:
Data Range
This array formula** entered in B2:
="("&concatall("'"&A2:A6&"'",",")&")"
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Thanks Tony....however now I'm getting the following error:
728474 #NAME?
1002208755
1001873448
783715
791490
Did you put the VBA code in the right place?
I am new to this so please bare with me. I copied the code in VB via insert module, then place the VBA code in cell B2. Now I'm coming up with the following:
('728474','1002208755','1001873448','783715','791490','','',''
How do you ignore the empty cells and add a parenthesis at the end?
Try this
=123456 & CHAR(10) & 235698 & CHAR(10) & 34567
Format the cell to wrap text, right click the cell=>select format cells select the Alignment tab and check Wrap Text
OMG, it worked. Thank you
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks