Text Split with no Delimiter

[Eric Tsang]

Dear all

I have encountered a problem with splitting the text without a delimiter

I have a list of names with their salutation, however, the colleague did not make a "space"after the salutation

Example

DrTsangEric
ProfEricTsang
MaryGoMs
TomhankMr

How can i split them as
Salutation Firstname Lastname as Dr Tsang Eric

Appreciate if you can enlighten me

Eric

[alansidman]

because there is no consistency in the location of the Salutation, the best I can offer is

Excel 2016 (Windows) 64 bit

	A	B	C	D	E
1	Column1		Column1.1	Column1.2	Column1.3
2	DrTsangEric		Dr	Tsang	Eric
3	ProfEricTsang		Prof	Eric	Tsang
4	MaryGoMs		Mary	Go	Ms
5	TomhankMr		Tomhank	Mr

Sheet: Sheet1

This is accomplished with Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[Eric Tsang]

Alan

Thanks but is there a formula instead?

Eric

[alansidman]

Because there is no consistency to the source data, I cannot think of any other way. There may be someone else out there who may be able to manage this.

EDIT: Because you are using O365, you have Flash Fill available to you. With that function found on the Data Tab you can get to this:

Excel 2016 (Windows) 64 bit

	A	B
1	Column1	Column2
2	DrTsangEric	Dr Eric Tsang
3	ProfEricTsang	Prof Tsang Eric
4	MaryGoMs	Mary Ms Go
5	TomhankMr	Tomhank Tomhank

Sheet: Sheet1

[Glenn Kennedy]

Here's a formula-based approach.

It's not perfect either. After running the formulae, use copy/paste values on all cells and then delete column C.

[Eric Tsang]

Dear Alan

You have inspired me.

I load all data into Power Query, and then use "column by example" by inputting

Dr Tsang Eric
Ms Mary Go

Rest it will convert ( i still do some manual work)

Thanks a lot

Eric

[AliGW]

Administrative Note

... please don't ignore contributors to your thread - acknowledge all solutions offered, even if they don't meet your requirements. Thanks.

[Eric Tsang]

Glenn

Thanks for your advice too

Eric

[Eric Tsang]

Ali

thanks, i have thanks Glenn as well

Eric