Import and export excel data having combined number and text in a single cell.

6 views (last 30 days)
I have following subset of data of size (3x1) stored in an excel file. I would like to import this data and write into another excel file.
4-sample1-sample2-(23.56,-13.54)
8-sample1-sample2-(5.35, 6.25)
15-sample1-sample2-(23.10, 2101.20)
Since each cell has combination of number, text and character datatype, may I know which function to use for such operation.
  3 Comments
Walter Roberson
Walter Roberson on 7 Jun 2022
A challenge with that format is that there is a risk that excel might see the leading digits and try to convert it to a number or a date, losing some information. The text representation in XML inside .xlsx files is prone to misinterpretation unless the field is clearly written as a character vector. For example "012" might be sent as a character vector but if not carefully encoded in the XML using a literal substitution, would likely be pulled back as 012 numeric and then having the leading 0 dropped to be read as numeric 12.
Because of this, when constructing text to be stored in xlsx files it is best to ensure that the text begins with a non-digit.

Sign in to comment.

Answers (1)

Peter Perkins
Peter Perkins on 13 Jun 2022
If each cell of your spreadsheet literally contains things like "4-sample1-sample2-(23.56,-13.54)", then you may want to figure out why and fix this at the source. This has to be like the most difficult data format ever.
Just for fun:
>> s = "4-sample1-sample2-(23.56,-13.54)"
s =
"4-sample1-sample2-(23.56,-13.54)"
>> s1 = split(s,"(")'
s1 =
1×2 string array
"4-sample1-sample2-" "23.56,-13.54)"
>> s11 = split(extractBefore(s1(1),strlength(s1(1))),"-")'
s11 =
1×3 string array
"4" "sample1" "sample2"
>> s12 = replace(split(s1(2),",")',["(" ")"],["" ""])
s12 =
1×2 string array
"23.56" "-13.54"
>> s3 = [s11 s12]
s3 =
1×5 string array
"4" "sample1" "sample2" "23.56" "-13.54"
There may be shorter ways, but that's what I came up with. So, read your spreadsheet into a 3x1 string array using readmatrix (you may need to tell it "string"), do the above on each row, to creat a 3x5 string array, then use writematrix.

Products


Release

R2011b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!