How to have this output and export to text file?

Or, =RIGHT(REPT("0",23)&A1*100&18700003,23)

maybe try this

	F	G	H	I
21	src	len	result
22	0.00	23	00000000000000018700003	=BASE(F22*100,10,15)&"18700003"
23	12140.86	23	00000000121408618700003
24	2500.00	23	00000000025000018700003
25	123.00	23	00000000001230018700003
26	9034569.00	23	00000090345690018700003
27	676767.00	23	00000006767670018700003
28	1111111111.00	23	00011111111110018700003

Originally Posted by Glenn Kennedy

Try:
=REPT(0,23-(LEN(100*A1)+8))&100*A1&18700003

copied down.

Thanks, this one works.

Originally Posted by sandy666

maybe try this

	F	G	H	I
21	src	len	result
22	0.00	23	00000000000000018700003	=BASE(F22*100,10,15)&"18700003"
23	12140.86	23	00000000121408618700003
24	2500.00	23	00000000025000018700003
25	123.00	23	00000000001230018700003
26	9034569.00	23	00000090345690018700003
27	676767.00	23	00000006767670018700003
28	1111111111.00	23	00011111111110018700003

Thank you.

You are welcome

Originally Posted by Glenn Kennedy

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

I have edited my post, I added another issue. I hope you can help me on this one too. Thank you.

It's because I need it to look exactly like the contents of the text file (kindly check) that I attached. That also includes concatenation and character spaces.

Originally Posted by Glenn Kennedy

IGNORE ME. I just spotted thta there are two attachments. Duhhh!

Yup, those attachments are kinda misplaced. Sorry.

there is no spaces before names (post#1, TextFile.txt)

Originally Posted by Glenn Kennedy

You can muck around with the spacing by adjusting the bits in RED:

=INPUT!A1&REPT(" ",5)&INPUT!B1&REPT(" ",25-LEN(INPUT!B1))&REPT(0,23-(LEN(100*INPUT!C1)+8))&100*INPUT!C1&18700003

If you make the 2nd one too short, you'll get an error.

I'm getting a different output Attachment 810623, the text copied from excel should look exactly like the top 3 items.

that's correct

Originally Posted by Glenn Kennedy

I thought I saw spaces between A & B. There weren't. And I've only been drinking tea...

=INPUT!A1&INPUT!B1&REPT(" ",25-LEN(INPUT!B1))&REPT(0,23-(LEN(100*INPUT!C1)+8))&100*INPUT!C1&18700003

Sorry, I'm still not getting the right output (spacing) please see this Attachment 810625. Thank you.

Originally Posted by sandy666

you attachment is invalid
could you explain why there is different number of spaces?
26, 21 and 24 ?
this is Excel not Word so you have to be exact

Kindly refer to this Attachment 810626. Thank you.

your attachment:

invalidattachment.png

again

read big yellow banner at the top of this page

Originally Posted by Glenn Kennedy

Sadly, my crystal ball is away for repair. If you don't TELL me the number of characters required (and preferably in Post 1) I can only guess.

=INPUT!A1&INPUT!B1&REPT(" ",40-LEN(INPUT!B1))&REPT(0,23-(LEN(100*INPUT!C1)+8))&100*INPUT!C1&18700003

Sorry, I have posted the picture in first post (edited). To answer Numbers before Name (10 characters), Name (Max 40 characters and has no spaces to the numbers before it) and Numbers with zeroes(23 characters).

I hope that clears everything up. Thank you.

you didn't answer on post#21

and use @username because we don't know to whom you are talking

Just tried that now, and it did the job! Thank you very much!

Originally Posted by Glenn Kennedy

Sadly, my crystal ball is away for repair. If you don't TELL me the number of characters required (and preferably in Post 1) I can only guess.

=INPUT!A1&INPUT!B1&REPT(" ",40-LEN(INPUT!B1))&REPT(0,23-(LEN(100*INPUT!C1)+8))&100*INPUT!C1&18700003

Just tried that now, and it did the job! Thank you very much!

Originally Posted by sandy666

your attachment is invalid
could you explain why there is different number of spaces?
26, 21 and 24 ?
this is Excel not Word so you have to be exact

There were exact spaces because it is a requirement of a program where I will be importing the file. I have updated my first post. Thank you very much for your patience and assistance

@kraddark
sure but don't quote whole posts just use @username, that's enough

additional solution with Power Query

Please Login or Register  to view this content.

as Glenn said you can use Copy/Paste into Notepad
or
save as text (.txt)
but there will appear a few questions and you have to choose wise what you want to do

so easer will be copy/paste