How do I export text with specified spacing between words?

Fucius

Board Regular
Joined
Jul 8, 2008
Messages
115
Hi again everyone,

I'm not sure if it's possible to do this in excel but How do I export text with specified spacing between words? I'm trying to figure this out for work so I don't have to manually type everything in, all I do is import this text file into our system. So anyway... I need to be able to export text containing 109 spaces preceding an ID consiting of 8 letters following by a description with preceding spaces of say 200 spaces.

ie: (109 spaces literally) -> ID [8 letters] -> (200 spaces) ->Description

Any help would be greatly appreciated! :)
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
What do you mean export? Export to where?

You can create 109 spaces using rept.

=REPT(" ", 109)

So something like this maybe? (Assuming ID is in B1 and description is B2)

=REPT(" ", 109) & B1 & REPT(" ", 200) & B2
 

Fucius

Board Regular
Joined
Jul 8, 2008
Messages
115
I need to be able to export the data as a text with the following format:

(109 spaces literally) -> ID [8 letters] -> (200 spaces) ->Description
 

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
Where are you getting ID and description? Assuming your ID and Description are in columns A and B and they have headers you can use this.

=REPT(" ", 109) & A2 & REPT(" ", 200) & B2

Copy down. Next copy and paste your new column as values, delete columns A & B, and save as text.

HTH,
Roger
 

Fucius

Board Regular
Joined
Jul 8, 2008
Messages
115

ADVERTISEMENT

rconverse,

SWEET! This works! I managed to get it to work but is there a specific place to paste your code? Do I paste it after inserting my column A & B data? if so, where? (Sorry, I'm a complete newb to this stuff) Thanks so much!
 

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
Assumng your ID is in colnm A and your description in B, you can insert that formula in colnm C and then copy down the spreadsheet.
 

Fucius

Board Regular
Joined
Jul 8, 2008
Messages
115
ahhh... okay, got it! This is awesome! thanks again so much, works like a charm! :LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top