CODEWITHSUNDEEP
excel
hardy
hardy

Reputation: 557

How to spilt data after year in excel?

I have lot of data in excel, In every row I have values like this: 

"Some text 2017 more text" 
"Some other text 2016 some more text"

and i want data like 

"Some text 2017  |   more text"
" Some other text  2016 | some more text"

Now i want to split this text after year, Every row has year name in it but not at the same position, so I can't use the split tool. Any help? Thanks.

Upvotes: 2

Views: 29

Answers (1)

pnuts
pnuts

Reputation: 59485

Assuming only the 2010 decade (2010-2019 bdi) then please try:

=REPLACE(A1,FIND("201",A1)+4,1,"|")

then Copy, Paste Special, Values over the top before Text to Columns with | as delimiter.

Upvotes: 1

Related Questions