Automatic Serial Number before Text in a Cell

[arijitirf]

Hi!
In Column D, data of Column F:O flows through Text Join skipping Blank cell. I want a formula that generates auto incremental serial number (1, 2, 3, 4 and so on).

Let's assume,

Data in Column
F as "Rameshwaram"
G as "Somenath"
H as "Kedarnath"
I as " "
J as "Baidyanath"
K as " "
L as " "
M as "Kashi Viswanath"
N as "Trimbakeshwar"
O as "Mahakaleshwar"

Then data in Column D will be
1. '"Rameshwaram"'
2. "Somenath"
3. "Kedarnath"
4. "Baidyanath"
5. "Kashi Viswanath"
6. "Trimbakeshwar"
7. "Mahakaleshwar"

Is there any way to do that. I have come across a excellent formula in this forum which can do the same for Row however, the same is not useful for me.

Thanks in advance

 

[Cubist]

Hi,

Which version of XL are you using?

 

[arijitirf]

Office 2016

 

[Joe4]

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 in the future.

 

[arijitirf]

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 in the future.

Done.

 

[Cubist]

I don't see an easy way to do it in 2016. Here's a minisheet for someone that can assist you further.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			1. Rameshwaram		Rameshwaram	Somenath	Kedarnath		Baidyanath			Kashi Viswanath	Trimbakeshwar	Mahakaleshwar
2			2. Somenath
3			3. Kedarnath
4			4. Baidyanath
5			5. Kashi Viswanath
6			6. Trimbakeshwar
7			7. Mahakaleshwar
8
Sheet2

 

[Cubist]

Are you open to VBA or strictly formula only?

 

[arijitirf]

I don't see an easy way to do it in 2016. Here's a minisheet for someone that can assist you further.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			1. Rameshwaram		Rameshwaram	Somenath	Kedarnath		Baidyanath			Kashi Viswanath	Trimbakeshwar	Mahakaleshwar
2			2. Somenath
3			3. Kedarnath
4			4. Baidyanath
5			5. Kashi Viswanath
6			6. Trimbakeshwar
7			7. Mahakaleshwar
8
Sheet2

Thank you for the Mini Sheet. Only thing is the data will flow in Column D1 for Data in between Column F1:O1.

 

[Cubist]

You mean everything in cell D1? like this?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			1. Rameshwaram 2. Somenath 3. Kedarnath 4. Baidyanath 5. Kashi Viswanath 6. Trimbakeshwar 7. Mahakaleshwar		Rameshwaram	Somenath	Kedarnath		Baidyanath			Kashi Viswanath	Trimbakeshwar	Mahakaleshwar
2
3
4
Sheet2

 

[arijitirf]

Are you open to VBA or strictly formula only?

VBA Code will be Okay for me. Only thing is that the data will flow in the D1 for Column F1:O1.