CODEWITHSUNDEEP
excel
Ben
Ben

Reputation: 1849

reformat excel text column to specific format

I have a column in my excel that includes authors name and it looks as follows:

enter image description here

My goal is to remove the dates + the last comma from all of these rows to make it something like this:

enter image description here

Is there a way I can do it in excel?

Upvotes: 0

Views: 62

Answers (1)

Witherfield
Witherfield

Reputation: 135

Based on your example, in which there are multiple commas in one cell, I would go with determining the position of the last comma first (in order to know where to slice the content of said cell). Then it's a matter of IF formula based on condition in which the last 4 characters in the cell are digits:

=IF(ISNUMBER(VALUE(RIGHT(A1,4))),LEFT(A1,FIND("@",SUBSTITUTE(A1,",","@",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1),A1)

FYI: The "@" substitution is targeted at knowing exactly where the last comma occurs in the cell. Any other unique, not-appearing-in-the-string character would have done the same job.

I've tested the formula on below examples:

enter image description here

Upvotes: 1

Related Questions