using space between number and text

[baidya91]

How can I rewrite the duration '[TABLE="width: 205"]
<tbody>[TR]
[TD="class: xl64, width: 205"]19years7months1days' in excel in the format '19 years 7 months 1 days' using space.

A1= [TABLE="width: 205"]
<tbody>[TR]
[TD="class: xl64, width: 205"]19years7months1days

B1= 19 years 7 months 1 days[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

UGLY, but

=MID(A1,1,2)&" "&MID(A1,3,5)&" "&MID(A1,8,1)&" "&MID(A1,9,6)&" "&MID(A1,15,1)&" "&MID(A1,16,4)

probably would be much simpler using Custom function !

 

[Rick Rothstein]

How can I rewrite the duration '[TABLE="width: 205"]
<tbody>[TR]
[TD="class: xl64, width: 205"]19years7months1days' in excel in the format '19 years 7 months 1 days' using space.

A1= [TABLE="width: 205"]
<tbody>[TR]
[TD="class: xl64, width: 205"]19years7months1days

B1= 19 years 7 months 1 days[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Give this a try...

[table="width: 500"]
[tr]
	[td]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"years"," years "),"months"," months "),"days"," days")[/td]
[/tr]
[/table]

 

[Michael M]

OMG.....obviously too much Xmas for me !!
nice one Rick....didn't even think of that..
A much smarter option....merry xmas and a happy NY to your Rick

 

[baidya91]

Thanks a lot