# Split cell

#### Steffmeister

##### Board Regular
Hi everybody,

I got a problem.
I got cells with large names in it and I wanted to split them.
I already do this with text to columns... buttt, I would like to place an space between '.comAddress'
I would like to seperate this into '.com' and 'Address', but when I fill in an 'A' with text to columns... then wrong cells will be seperated, so I just wanted that first a space will be added between all the '.comAddress'.

Is this possible? If yes, could somebody plsss help me ?

Kind regards,

Stephan

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### NathanPage

##### New Member
One way to do this is as follows.

Let us say that the first cell with the long address in it is cell A1. In cell B1 write the formula:

=LEFT(A1,SEARCH(".com",A1)+3)

and in cell C1:

=MID(A1,SEARCH(".com",A1),50) (+50 should be larger, if the text that might appear as "Address" could be more than 50 characters).

Cell B1 will then have all text from cell A1, up to and including ".com". C1 will have all text following ".com".

The formulae can be copied down.

Hope that works,

#### RAM

##### Well-known Member
Stephan,

You already knows how to Text to Columns.

You can separate the word into two columns and in column C write
=CONCATENATE(A1," ",B1), copy down and then delete column A and B.

HTH

RAM

#### MichaelRo

##### Well-known Member
Looking at Nathan's reply he hasn't removed the .com in his formula

=MID(A1,SEARCH(".com",A1),50) will return .comaddress from Steffmeister's example

Assuming your data in A1:

=LEFT(A1,SEARCH(".com",A1,1)+3) & " " &MID(A1,SEARCH(".com",A1,1)+4,LEN(A1))

#### Steffmeister

##### Board Regular

Hi NathanPage,

I tried the code but is won't work for me. It says that the code contains an error. Do you know how that's possible?

grts. Stephan

#### Steffmeister

##### Board Regular
Hi guys,

Here I got an example.
In Column A there are addresses like this:

I would like to have deleted Address:
So finally there gets

Name: dhcp-9-20-148-192.com 9.20.148.192

Sorry if I explain it wrong

Kind regards,
Stephan

#### RAM

##### Well-known Member

I would do it as I explained above. Text to Columns, Fixed width, put two break lines on each side of the word Address: select (click on) the column Address: and select the option Do not import (skip) and Finish. That will divide the left over into two columns and put them together again with my formula above, copy down and delete the two first columns.

I know it will be more work than one formula but it works.

HTH

RAM

#### Steffmeister

##### Board Regular
Hi Ram

I must do that for each address because all addresses have got different lengths right?. Maybe another solution is possible? Is it possible to delete the last 8 characters in a cell with a macro or something? Then it is possible to delete ‘Address:’ because I will separate the ip address into other column with text to column…

Grts. Stephan

#### bosco_yip

##### Well-known Member
Assume your data in A1, cell B1 enter :

#### Steffmeister

##### Board Regular
I don't now why, but I get errors with all the formula's.
I don't doing something wrong, does somebody know how that is possible?

Grts. Stephan

Replies
12
Views
129
Replies
2
Views
173
Replies
10
Views
249
Replies
8
Views
151
Replies
4
Views
232