What's Formula Separate Text In 3 Space

[Joko Purnomo]

What's Formula Separate Text In 3 Space Front, For More Text or Sentence

Example :
1. Showroom Maju Jaya Auto Palembang Sukabangun
2. Showroom Andi Naya Rivera Medan Markisa

Please Help me, not one by one formula

Tks

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You have given 2 examples but the requirement is not clear to me. Could you give the expected results for those 2 examples and give any further clarification about how you got those results?

 

[Joko Purnomo]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You have given 2 examples but the requirement is not clear to me. Could you give the expected results for those 2 examples and give any further clarification about how you got those results?

Examples :

1. Showroom Maju Jaya Auto Palembang Sukabangun expected results : Showroom Maju Jaya Auto
2. Showroom Andi Naya Rivera Medan Markisa expected result : Showroom Andi Naya

what is the formula for the two examples, which can be used simultaneously with the same formula. 4 words with 3 spaces from the front.

if we use the "mid" formula it can be different for the two examples, I want the same formula for expected results in each example above. Help me another formula.

Thanks

 

[Peter_SSs]

what is the formula for the two examples, which can be used simultaneously with the same formula. 4 words with 3 spaces from the front.

I still do not understand since you state here 4 words with 3 spaces but your second example expected result is only 3 words with 2 spaces.

 

[Joko Purnomo]

I still do not understand since you state here 4 words with 3 spaces but your second example expected result is only 3 words with 2 spaces.

Yes i mean 3 words with 2 spaces, help me sir

 

[Peter_SSs]

Yes i mean 3 words with 2 spaces, help me sir

So how do we work out which data you want 4 words with 3 spaces and which data you want 3 words with 2 spaces?

For example, what would be the expected result for this data, and why?
one two three four five six

 

[Joko Purnomo]

So how do we work out which data you want 4 words with 3 spaces and which data you want 3 words with 2 spaces?

For example, what would be the expected result for this data, and why?
one two three four five six

1. Showroom Maju Jaya Auto Palembang Sukabangun expected results : Showroom Maju Jaya Auto
2. Showroom Andi Naya Rivera Medan Markisa expected result : Showroom Andi Naya Rivera

Expexted result 4 words with 3 space, because i have more case like this with different text

Thank for helping

 

[SunnyAlv]

1. Showroom Maju Jaya Auto Palembang Sukabangun expected results : Showroom Maju Jaya Auto
2. Showroom Andi Naya Rivera Medan Markisa expected result : Showroom Andi Naya Rivera

Expexted result 4 words with 3 space, because i have more case like this with different text

Thank for helping

pemisah nya nama kota ? sekalian versi excelnya pak cantumkan

 

[kevin9999]

If you have 365, use the formula in B1. Otherwise, try the formula in B2, and please update your profile to show what version of Excel you're using so we don't have to guess.

text before.xlsx
	A	B
1	Showroom Maju Jaya Auto Palembang Sukabangun	Showroom Maju Jaya Auto
2	Showroom Andi Naya Rivera Medan Markisa	Showroom Andi Naya Rivera
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTBEFORE(A1," ",4)
B2	B2	=LEFT(A2,FIND("|",SUBSTITUTE(A2," ","|",4))-1)

 

[Joko Purnomo]

pemisah nya nama kota ? sekalian versi excelnya pak cantumkan

Pemisah tidak berpatokan nama kota, yang penting 4 kata dari depan. Excel 2016