10

For some reason, when I import data from a certain unnamed source (toggl.com), some of the time columns have an apostrophe in them.

When I have this data loaded in Calc, is there a quick, easy way to remove these?

The columns look like:

'12:05:12
'05:05:05
'04:04:44

And I want:

12:05:12
05:05:05
04:04:44

I see the =REPLACE function, but I can't seem to get that to work. Maybe I don't understand the steps.

mtyson
  • 699
  • 2
  • 7
  • 12

5 Answers5

10

If your strings are of unequal length you can use the mid() and len() commands combined.

In your example the command would be: =MID(A1,2,LEN(A1))

Drag it down as in the answer above.

How does it work?

MID returns a sub-string of a string specified as: MID([string], [start index], [end index]). If the [end index] varies, using LEN(A1) specifies the length of the particular string.

jnovack
  • 1,426
Frank P
  • 101
7

Just use =right(A1,8) and drag it down. Right will always give you the X rightmost characters of the string and given it's consistent in this string, it will be the simplest solution.

Raystafarian
  • 21,743
  • 12
  • 62
  • 90
4

The function you are looking for is the text command, SUBSTITUTE("Text"; "SearchText"; "NewText"; Occurrence)

Chris Laidler
  • 141
1

You may try Search For and Replace With (Ctrl+h). In other options check Regular expressions.

Matthias Braun
  • 1,280
Erdem
  • 11
-2

For me it was cell formating try using Ctrl+M

dehit
  • 1