• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

How remove all characters after a period including the period

I several cell in a column I have data that reads for example:

04022.xls
or
05123.xls
or
123456.xls (note six numbers before the dot)
or
1234.xls (note four numbers before the dot)

I want to keep everything BEFORE the dot and paste it in a new column to the right of the existing column.  How can I do this?

--Steve
0
SteveL13
Asked:
SteveL13
  • 2
1 Solution
 
nutschCommented:
Assuming your data starts in cell A2, put this formula in B2, copy down all the way then do a copy / paste values. FIND finds the position of the dot, and left returns the characters left of that position.

=LEFT(a2, find(".",A2)-1)

Thomas
0
 
Santosh GuptaCommented:
Hi,

select the column and press Ctrl+H

Type in find:     .*               (dot and Star only)
keep replace as blank.

and click on replace All.
0
 
nutschCommented:
For Santosh's solution, copy your data to a new column first and select that new column before the Replace (Ctrl+H) if you want the tweaked data in a separate column.

Thomas
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now