Copy Paste (spesial case)

[habib81]

I want simplify my work. Please help me to give me some macro or other to change:

464583xxxxxx2908 500,000
4645831xxxxxx687 5,973,098
464583xxxxxx1904 428,240

Become

"464583xxxxxx2908
[tab field]
"500000
[enter]
"4645831xxxxxx687
[tab field]
"5973098
[enter]
"464583xxxxxx1904
[tab field]
"428240
[enter]


The row usual reached 1000 record so I need the macro to make it easily.

BR

[RusselB]

I don't know about a macro (lack of knowledge on my part), is there some reason why you want/need the [tab field] and [enter] items in your output?
If not, you can get the results by simply removing the formatting from all entries in column B of your attached spreadsheet.
If you do, then all you need is a single helper column with the formula

Code: Select all

=if(istext(A1);A1;text(A1;"#"))&char(10)&if(istext(B1);B1;text(B1;"#"))&char(13)

[habib81]

Russel B

I have modify your code below

IF(ISTEXT(A1);A1;TEXT(A1;"#"))&CHAR(10)&"[tab field]"&CHAR(10)&IF(ISTEXT(B1);B1;TEXT(B1;"#"))&CHAR(13)&"[enter]"

And it has successfully. The result is below:

464583xxxxxx2908
[tab field]
500000
[enter]
4645831xxxxxx687
[tab field]
5973098
[enter]
464583xxxxxx1904
[tab field]
428240
[enter]

But how to put double apostrophe (") in front of numbers and amounts?

BR

[RusselB]

Sorry, I didn't realize that you literally wanted those words, and not just the effect of the related character codes.
To add the " (properly called double quotes, not double apostrophe), adjust your modified code to prefix the A1 and B1 entries with CHAR(34)

Note: I was unsure if your entries were always going to be string or numerics, thus the usage of the ISTEXT function.
If you know that the first entry will always have the x's in it (I'm suspecting credit card numbers) and the second field will always be numeric, then you could make your final code be

Code: Select all

=concatenate(char(34);a1;char(10);"[tab field]";char(10);char(34);text(b1;"#");char(13);"[enter]")

[habib81]

Dear Russel B

It a great day to me. I know ASCII code today and I can use it from you.

I have modify it again and the result is same with yours like below

IF(A1=A1;CHAR(34)&A1;"")&CHAR(10)&"[tab field]"&CHAR(10)&IF(ISTEXT(B1);B1;CHAR(34)&TEXT(B1;"#"))&CHAR(10)&"[enter]"

Your guess it true. The first number is credit card number and second is payment amount. I will use macro my payment system to simplify my work that very much and much.

I am settlement officer in one bank on Indonesia.

Thx

[RusselB]

The first IF in the latest code you posted is irrelevant, since you are comparing A1 to itself, thus the result will always be true.
Since my presumptions about the types of data were correct, the last code I posted does not require modification for your situation