Removing spaces from middle of cell
 

In my spreadsheet there is a column that contains number, 3 on the left,
space, 3 on the right. I want to remove that space. Rather than doing this
in three steps, taking left, then taking right, then joining those together,
is there an easier way to accomplish the same thing.

It currently looks like this 216 123
I want it to be this 216123

I'm sure there is an easier way, but I only know how in three steps.

Thanks!

Answer: Removing spaces from middle of cell
 

Yes, there is an easier way to remove the space from the middle of a cell in Excel. You can use the SUBSTITUTE function to replace the space with nothing. Here are the steps to do it:

1. Select the column that contains the cells with the spaces you want to remove.
2. Click on the "Home" tab in the ribbon.
3. Click on the "Find & Select" button in the "Editing" group, and then select "Replace" from the dropdown menu.
4. In the "Find what" field, type a space character (press the spacebar once).
5. Leave the "Replace with" field blank.
6. Click on the "Replace All" button.
7. Click on the "OK" button to confirm that you want to replace all instances of the space character with nothing.

This will remove all spaces from the selected column, leaving only the numbers. Note that this will replace all spaces in the column, so make sure you don't have any spaces that you want to keep.

Removing spaces from middle of cell
 

Hi,

One way is to use the formula below in a helper column and then copy the
helper column and paste special|Values back over the original data. you can
then delete the helper column

=SUBSTITUTE(A1," ","")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"TJAC" wrote:

In my spreadsheet there is a column that contains number, 3 on the left,
space, 3 on the right. I want to remove that space. Rather than doing this
in three steps, taking left, then taking right, then joining those together,
is there an easier way to accomplish the same thing.

It currently looks like this 216 123
I want it to be this 216123

I'm sure there is an easier way, but I only know how in three steps.

Thanks!

Removing spaces from middle of cell
 

On Wed, 21 Apr 2010 09:27:02 -0700, TJAC
wrote:

In my spreadsheet there is a column that contains number, 3 on the left,
space, 3 on the right. I want to remove that space. Rather than doing this
in three steps, taking left, then taking right, then joining those together,
is there an easier way to accomplish the same thing.

It currently looks like this 216 123
I want it to be this 216123

I'm sure there is an easier way, but I only know how in three steps.

Thanks!

Another way would be to use FIND/REPLACE
Find what: <space
Replace with: "just leave this blank"

--ron

Removing spaces from middle of cell
 

I wasn't even thinking of something that simple. Thanks!

"Ron Rosenfeld" wrote:

On Wed, 21 Apr 2010 09:27:02 -0700, TJAC
wrote:

In my spreadsheet there is a column that contains number, 3 on the left,
space, 3 on the right. I want to remove that space. Rather than doing this
in three steps, taking left, then taking right, then joining those together,
is there an easier way to accomplish the same thing.

It currently looks like this 216 123
I want it to be this 216123

I'm sure there is an easier way, but I only know how in three steps.

Thanks!

Another way would be to use FIND/REPLACE
Find what: <space
Replace with: "just leave this blank"

--ron
.

Removing spaces from middle of cell
 

Much easier and faster than mine. Thank you!

"Mike H" wrote:

Hi,

One way is to use the formula below in a helper column and then copy the
helper column and paste special|Values back over the original data. you can
then delete the helper column

=SUBSTITUTE(A1," ","")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"TJAC" wrote:

In my spreadsheet there is a column that contains number, 3 on the left,
space, 3 on the right. I want to remove that space. Rather than doing this
in three steps, taking left, then taking right, then joining those together,
is there an easier way to accomplish the same thing.

It currently looks like this 216 123
I want it to be this 216123

I'm sure there is an easier way, but I only know how in three steps.

Thanks!

Removing spaces from middle of cell
 

On Wed, 21 Apr 2010 10:56:01 -0700, TJAC
wrote:

I wasn't even thinking of something that simple. Thanks!

Glad to help. Thanks for the feedback.
--ron