using space between number and text

[baidya91]

How can I rewrite the duration '

19years7months1days' in excel in the format '19 years 7 months 1 days' using space. A1= 19years7months1days B1= 19 years 7 months 1 days <tbody> </tbody>

<tbody>
</tbody>

 

[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 '

19years7months1days' in excel in the format '19 years 7 months 1 days' using space. A1= 19years7months1days B1= 19 years 7 months 1 days <tbody> </tbody>

<tbody>
</tbody>

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