How to add a space after each text value in all the cells in a row

P

Peter

I want to add a space (or any character(s)) after each text value in all the
cells in a row or in a column

For example, cell A2 contains "ABC", cell B2 contains "DEF", and so on. I
want to make all the cells in row 2 to have a space so cell A2 contains "ABC
", cell B2 contains "DEF ",...
 
A

Access Noob

try going to an empty part of the sheet (say row a4) & type =a2&" " or "[any
character]" then copy that across the row for your range. after calc you can
copy the range & paste special (values) back into range a2 ...X2 & your
titles will have a trailing space [any character].
 
P

Peter

Thanks. Is there a way to copy the formula (=a2&" ") into all the cells or
range of cells in a row instead of copying the formula into one cell at a
time?

Access Noob said:
try going to an empty part of the sheet (say row a4) & type =a2&" " or "[any
character]" then copy that across the row for your range. after calc you can
copy the range & paste special (values) back into range a2 ...X2 & your
titles will have a trailing space [any character].

Peter said:
I want to add a space (or any character(s)) after each text value in all the
cells in a row or in a column

For example, cell A2 contains "ABC", cell B2 contains "DEF", and so on. I
want to make all the cells in row 2 to have a space so cell A2 contains "ABC
", cell B2 contains "DEF ",...
 
J

Jacob Skaria

Select the range say A3:J3. A3 being the active cell; keeping the selection
press F2 and edit/enter formula in A3. Keeping the selection Hit Ctrl+ Enter
instead of Enter..The formula will be applied to all cells in the selection.

--
Jacob


Peter said:
Thanks. Is there a way to copy the formula (=a2&" ") into all the cells or
range of cells in a row instead of copying the formula into one cell at a
time?

Access Noob said:
try going to an empty part of the sheet (say row a4) & type =a2&" " or "[any
character]" then copy that across the row for your range. after calc you can
copy the range & paste special (values) back into range a2 ...X2 & your
titles will have a trailing space [any character].

Peter said:
I want to add a space (or any character(s)) after each text value in all the
cells in a row or in a column

For example, cell A2 contains "ABC", cell B2 contains "DEF", and so on. I
want to make all the cells in row 2 to have a space so cell A2 contains "ABC
", cell B2 contains "DEF ",...
 
D

David Biddulph

Either:
Copy, select your range on the sheet, Paste,
or
Copy, type the reference of your range into the name box, Paste,
or
Click on the bottom right hand corner of the cell to get a square fill
handle, then drag that across your range,
or ...
--
David Biddulph

Peter said:
Thanks. Is there a way to copy the formula (=a2&" ") into all the cells
or
range of cells in a row instead of copying the formula into one cell at a
time?

Access Noob said:
try going to an empty part of the sheet (say row a4) & type =a2&" " or
"[any
character]" then copy that across the row for your range. after calc you
can
copy the range & paste special (values) back into range a2 ...X2 & your
titles will have a trailing space [any character].

Peter said:
I want to add a space (or any character(s)) after each text value in
all the
cells in a row or in a column

For example, cell A2 contains "ABC", cell B2 contains "DEF", and so on.
I
want to make all the cells in row 2 to have a space so cell A2 contains
"ABC
", cell B2 contains "DEF ",...
 
D

Dave Peterson

You have suggestions on how to accomplish this, but this kind of thing is
usually bad.

If you have formulas like:

=if(a2="abc","something","something else")

Then they won't return what you expect. You'll either have to modify all(!) the
formulas to look like:

=if(trim(a2)="abc",...)

Or fix the data again.

(And looking at the value in the formula bar isn't enough to notice that extra
space character.)

If you're doing this to match another table (imported from a different
application???), then you could change the formulas to include the space
character:

=vlookup(a2&" ",sheet99!a:b,2,false)

Even then, this is a clumsy fix (I think). Instead I'd clean up that original
data so that it didn't have those extra trailing spaces.

If you needed something like that, you could ask and you'll get tons of
responses.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top