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

6 ビュー (過去 30 日間)
Zee 2022 年 6 月 6 日
回答済み: Peter Perkins 2022 年 6 月 13 日
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.
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 件のコメント
Walter Roberson
Walter Roberson 2022 年 6 月 7 日
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.


回答 (1 件)

Peter Perkins
Peter Perkins 2022 年 6 月 13 日
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 =
>> 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.


Help Center および File ExchangeSpreadsheets についてさらに検索




Community Treasure Hunt

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

Start Hunting!

Translated by