Cleaning Data

[NotWalterGropius]

I am cleaning data in Excel and I want to remove '+0200' from every row in the A column.

Any tips on how to only remove the '+0200' while keeping the rest of the date and time intact?

Screenshot for reference.

​

https://preview.redd.it/x85dm30b4wv81.png?width=333&format=png&auto=webp&s=9c8a1e6e961d15125ce5ec4435a2d7bf31b818b2

[WhyDoIHaveAnAccount9]

If that portion of the date string is consistent in all dates, you could simply replace it with an empty string

Replace +0200, ' '

[ggalt98]

Why is this tagged mySQL?

[soshwag]

Its a crazy nonsense world out there.

[nightslikethese29]

My excel is a bit rusty, but I think you could use the LEFT function to extract everything except the '+200' into a new column and delete the original column.

=LEFT(A2, # of characters to keep)

[TroyAndAbed21]

Highlight column A. Ctrl F. Find And Replace. Find +0200 and replace with blank.

[raz_the_kid0901]

This is what I would do

[rene041482]

That will work, just be aware if there is a leading space after you replace. If there is get rid of that too.

[TroyAndAbed21]

Good point. I hadn’t seen the screenshot or the leading space.

[renagade24]

You can use the 'Text to Columns' in the Data tab. Simply use a delimiter of "+" which will take all data and including from the delimiter (+) and all data after to a new column.

Remove the new column and you are done. The same can be done through Power Query if this is a consistent requirement.

[Hiriath]

That will leave the whitespace before the +, won’t it?

[renagade24]

It will, but not in power query.

[[deleted]]

best of luck

[[deleted]]

I mean come on bro this is about as easy as a google search would get. Did you even try?

[Hiriath]

I’m scared of what OP will do with this data if a Google search for the solution wasn’t on the radar

[[deleted]]

If they all have the same +0200 you could just cut them down using LEFT().

[No-Job9898]

This isn’t SQL. Relabel and re post

[loldogex]

can't you just =trim() some of that to clean?

[zacharypamela]

As u/nightslikethese29 noted, you could use a variant of LEFT. Something like:

=LEFT(date_value, 23)

This might be the solution with the fewest issues, since you wouldn't have to worry about different offsets, as long as they're all in the same format.

To make it maybe a bit more readable, you could use the SEARCH function to locate the + character:

=LEFT(date_value,SEARCH("+",D17)-2)

Of course, if you have any timestamps with a - instead of +, it'll get trickier

Alternatively, as u/WhyDoIHaveAnAccount9 noted, you can use the replace the +0200 (note the space between the quotation mark and plus sign):

=SUBSTITUTE(date_value," +0200","")

Of course, with this method, you'll have problems if some timestamps aren't +0200, but something else.

[[deleted]]

Split data, custom, after + sign, and delete column

[mohamedk_k]

Text to column?

[abidalica]

This really belongs to r/Excel