Text with trailing spaces

I need formula to convert text in cell to 10 characters in length inside quotes.


value in A1 B1000 to "B1000     "
cebu1014Asked:
Who is Participating?
 
ZShaverConnect With a Mentor Commented:
you can set B1 to:
=A1&REPT(" ",10-LEN(A1))

and B1 will have the padded value

however you cannot place the formula in A1 because that cell contains the data and this would create a circular reference
0
 
ZShaverCommented:
why would you want to do this anyways, doesn't make a lot of sense, maybe if you give us an explanation of the problem we can come up with better solution.
0
 
cebu1014Author Commented:
The result will be copied to text file for importing into program.
Can I  get the display to show "B1000    " in excel?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
dlmilleCommented:
Not for points:

In case cell A1 has trailing spaces in it, already, I would recommend a slight modification to ZShaver's tip:

[B1]=TRIM(A1)&REPT(" ",10-LEN(TRIM(A1)))

and copy down.  You can then copy B column and paste value to A column



Cheers,

Dave
0
 
ZShaverCommented:
Can I  get the display to show "B1000    " in excel?
well, you can't see spaces, but if u underline the cell you can see it is working
0
 
wchhCommented:
If you have mutiple columns,
You may adjust the column width, for example 10.
Save as "*.prn" file.
The column will be displayed as 10 characters include spacing.
0
 
Otto_NConnect With a Mentor Commented:
I might be missing the point, but it seems that you want to include the double-quotes in the display?  Then just concatenate "CHAR(34)" before and after ZShaver or dlmille's suggestion, i.e
B1=CHAR(34) & A1&REPT(" ",10-LEN(A1)) & CHAR(34)
  or
B1=CHAR(34) & TRIM(A1)&REPT(" ",10-LEN(TRIM(A1))) & CHAR(34)

This will return the text string including quotes.  Just note that, if the input text string is longer than 10 characters, you will receive a "#VALUE"-error.
0
All Courses

From novice to tech pro — start learning today.