Excel Forum

Extract text from cell after specific character

Sharas 4ever

New Member

Members

Forum Posts: 2

Member Since:
September 19, 2018

Offline

September 19, 2018 - 6:51 pm

Hello,

I want to extract country information from cell.
Lines number in cell may be different. But there always will be prefix "Country: " in this line.

Could you please help me on this?

A1:
Name: Adam
Surname: Jones
Email: a.jones@mail.com
Company: Adam Company UTD
Job title: Database Manager
Phone: +123456789
Country: United States
-random text-
Registered

A2:
Name: John
Surname: Doe
Email: john.doe@mail.com
Company: Doe and Sons
Job title: Mechanical Engineer
Phone: 00123456789
Country: United Kingdom
-bla bla bla-
random text
registration

Wanted result:

B1:
United States

B2:
United Kingdom

SunnyKow

Puchong, Malaysia

VIP

Members

Trusted Members

Forum Posts: 1432

Member Since:
June 25, 2016

Offline

September 20, 2018 - 1:19 am

It will depend if all countries are two-worded like in your examples.

If not then it may be a bit difficult.What is the text after the country's name?

Better if you could provide more dummy data in a attachment for us to take a look.

Sunny

Sharas 4ever

New Member

Members

Forum Posts: 2

Member Since:
September 19, 2018

Offline

September 20, 2018 - 11:47 pm

Not all countries are two worded.

The text after the country might be various. There are no specific word or symbol after country information.

But this text starts in new line.

SunnyKow

Puchong, Malaysia

VIP

Members

Trusted Members

Forum Posts: 1432

Member Since:
June 25, 2016

Offline

September 21, 2018 - 12:39 pm

Give this a try. Not too sure if it will work under all circumstances.

I am assuming that CHAR(10) is used to break the line. You may need to change this if it is different.

Columns B-E are the detailed step-by-step workings. Column F is the mega formula (all in one)

Hope this helps.

Sunny

Forum Timezone: Australia/Brisbane

Most Users Ever Online: 245

Currently Online: Alan Sidman, Ngoc Tinh, Dieneba NDIAYE, Alexandra Radu, Natasha Smith, Monique Roussouw

Guest(s) 10

Currently Browsing this Page:
1 Guest(s)

Top Posters:

SunnyKow: 1432

Anders Sehlstedt: 870

Purfleet: 412

Frans Visser: 346

David_Ng: 306

lea cohen: 219

A.Maurizio: 202

Jessica Stewart: 202

Aye Mu: 201

jaryszek: 183

Newest Members:

michael serna

mashal sana

Tiffany Kang

Leah Gillmore

Sopi Yuniarti

LAFONSO HERNANDEZ

Hayden Hao

Angela chen

Sean Moore

John Chisholm

Forum Stats:

Groups: 3

Forums: 24

Topics: 6216

Posts: 27250

Member Stats:

Guest Posters: 49

Members: 31897

Moderators: 3

Admins: 4

Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT

Moderators: MOTH Support, Velouria, Riny van Eekelen