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

  • Thread starter Thread starter Peter
  • Start date Start date
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 ",...
 
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].
 
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 ",...
Click to expand...
Click to expand...
 
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 ",...
Click to expand...
Click to expand...
Click to expand...
 
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.
 
Back
Top