Split cell

[Steffmeister]

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

 

[NathanPage]

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]

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]

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]

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]

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

Kind regards,
Stephan

 

[RAM]

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]

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]

Assume your data in A1, cell B1 enter :

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

 

[Steffmeister]

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