Tuesday, April 19, 2011

yogi_Splitting Text Entry With Numerics Without Suppressing Leading Zeros

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

QE2 said:
Spreadsheet field contains property parcel numbers in the format:  a99 999 999. I am using SPLIT formula =SPLIT(A1," ") to break the field apart into three chunks each in a separate column. SPLIT is deleting leading zeros on numeric chunks. I have set all columns to "plain text". Still deletes leading zeros. Please advise. 
Here is some data:col A: D02 003 030 
incorrect result: 
col B: D02 col C: 3 col D: 30
desired result: 
col B: D02 col C: 003 col D: 030


In the following solution in Sheet1, I have assumed that the string to be split starts with a non-zero character