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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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,940
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,546
Messages
5,838,029
Members
430,526
Latest member
NiceGuyWithExcel2007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top