• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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     "
0
cebu1014
Asked:
cebu1014
2 Solutions
 
ZShaverCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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_NCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now