Split cell

Steffmeister

Board Regular
Joined
Nov 7, 2005
Messages
195
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
 

Some videos you may like

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
Joined
Dec 8, 2005
Messages
9
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
Joined
Oct 4, 2004
Messages
1,862
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
Joined
Jun 7, 2004
Messages
549
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
Joined
Nov 7, 2005
Messages
195

ADVERTISEMENT

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
Joined
Nov 7, 2005
Messages
195
Hi guys,

Here I got an example.
In Column A there are addresses like this:
Name: dhcp-9-20-148-192.comAddress: 9.20.148.192

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 :oops:

Kind regards,
Stephan
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862

ADVERTISEMENT

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
Joined
Nov 7, 2005
Messages
195
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
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
Assume your data in A1, cell B1 enter :

=SUBSTITUTE(A1,"comAddress","com")
 

Steffmeister

Board Regular
Joined
Nov 7, 2005
Messages
195
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,229
Messages
5,571,019
Members
412,354
Latest member
Stj99
Top