Excel Questions

Forums › Off topic › Excel Questions

networkn

Networkn
32349 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#102782 22-May-2012 14:30

Hi There!

If I have a column of numbers but they are all different, and I want to add a SPACE to the end of the number, how would I do that to everything in that column? Same with a column with text in it?

1 | 2

732 posts

Ultimate Geek

#628989 22-May-2012 14:34

You could do this as a formula inserted into another column?
but what is the purpose of this?
is there anything after the number?

Doing your best is much more important than being the best.

networkn

Networkn
32349 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#628991 22-May-2012 14:36

No, but for the sake of importing the text into another application I need a space, as when I concatenate the columns it's not formatted nicely.

wellygary

8312 posts

Uber Geek

#628992 22-May-2012 14:39

Create a column containing the SPACEs and then use CONCATENATE to join to contents of the two columns together..

Bee

732 posts

Ultimate Geek

#628994 22-May-2012 14:41

or just : =CONCATENATE(A1," ")

EDIT : or creating a custom number format as 0 then a space and applying that will work too :) without having to create a new column

:)

Doing your best is much more important than being the best.

networkn

Networkn
32349 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#628996 22-May-2012 14:42

That's what I am trying to do. I don't know how to apply a space to the end of the columns

networkn

Networkn
32349 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#628997 22-May-2012 14:42

Bee: that should work :)

gzt

17104 posts

Uber Geek

Lifetime subscriber

#628998 22-May-2012 14:43

Presumably you want a consistent number of spaces and not enter each manually. Custom number format:

http://office.microsoft.com/en-us/excel-help/number-format-codes-HP005198679.aspx

Equinox IT

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.

russelo

328 posts

Ultimate Geek

#629007 22-May-2012 15:11

Use ampersand to append values as text:
= A1 & " quick " & A2 & " fox"

In your case:
=A1 & " "

NonprayingMantis

6434 posts

Uber Geek

#629021 22-May-2012 15:30

depends whether you need the space there for some other operation, or whether you just want the appearanceof a space.
If you need the actual space for, say, consolidating the data on another sheet or something, then use the formulas above.
If you just want it for aesthetic purposes, then just custom format it. Much much easier and less error prone.

networkn

Networkn
32349 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#629046 22-May-2012 16:22

Well when I import the column into our accounting system, I want the spaces there so it's readable under description.

gzt

17104 posts

Uber Geek

Lifetime subscriber

#629120 22-May-2012 19:21

I don't have excel on this machine. Someone who does might be able to knock this into proper shape:

If(LEN(A1)=1,B1=A1 & " "); If(LEN(A1)=2,B1=A1 & " "); If(LEN(A1)=3,B1=A1 & " "); [etc]

gzt

17104 posts

Uber Geek

Lifetime subscriber

#629143 22-May-2012 20:02

networkn: Well when I import the column into our accounting system, I want the spaces there so it's readable under description.

A custom format would appear that way in an export from excel to csv or whatever. Depends how your accounting program reads and processes the import I guess. Custom format might work.

wasabi2k

2096 posts

Uber Geek

#629403 23-May-2012 11:33

also saving out to csv may remove the padding spaces depending on the format of the column (number vs text)

networkn

Networkn
32349 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#629409 23-May-2012 11:42

Hmm reformatting this spreadsheet is no small task! I also need to add the word user to every cell in a column now too, could someone show me how to do that please? It's a cell with numbers and I just need a space and then "user" I am thinking it will need to be something like =a1+ " User" or something? I wish it was possible to modify the existing cells rather then adding a new column of cells

NzBeagle

961 posts

Ultimate Geek

Trusted

#629416 23-May-2012 11:50

networkn: Hmm reformatting this spreadsheet is no small task! I also need to add the word user to every cell in a column now too, could someone show me how to do that please? It's a cell with numbers and I just need a space and then "user" I am thinking it will need to be something like =a1+ " User" or something? I wish it was possible to modify the existing cells rather then adding a new column of cells

Using a VB script you could set it up to run for the column and replace existing cells with the desired contents

1 | 2