1

I have 4000 rows in excel and I need to remove all text after the first period in each row in the column.

For example I have something like the following:

hostname.subdomain.domain

I want all the ones formatted like this to turn out to just be hostname. Therefore I want to eliminate the period and anything after it, while keeping the text before it.

2
  • OK with moving to a new cell and hiding the old cell? Or does it need to be actually changed in the original cell? The first could be a formula, the second would be VBA – Austin T French Oct 20 '13 at 23:16
  • @AthomSfere I just need to strip out the data. I don't need the old data after it has been stripped. – ComputerLocus Oct 20 '13 at 23:17
5

I will through this up for a function.

=LEFT(A2,SEARCH(".",A2,1)-1)

Where A2 would be the host.domain.com. You could then mass paste this and Hide A2, leaving in the new cell just the host name

If you want to just over write the existing cell, then it would have to be VBA.

3

You may find "text to columns" useful. Select the column with your data. On Data tab, click "text to columns", "delimited", and use a period as your delimiter.

This will split all the segments into individual cells, and you can keep or discard whatever you like. Be warned, it will overwrite any adjacent cells, so you may want to copy the column to a new sheet first.

0

If you just want to "replace" all these values by the text before the dot this is the easiest way:

Just select the column, press Ctrl+H (for replace), enter .* in the "Find what" box and leave the "Replace with" box blank. Apply by clicking "Replace All".

1
  • This simply restates an existing answer that has already been downvoted as it will not achieve the desired result. – Chenmunka Jun 5 '14 at 8:48
-1

You should replace .* with nothing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.