0

I have an external application which exports table data to the excel value. Every cell has numeric value but exported in text format. Some values in table are integer, some float, some exponential, some should remain in text format.

Tell me please, is there way to automatically recognize and set format of values? We should possibly consider cell column because value matched to the same mask should have different format. For example:

'12345' => 12345 in one column
'12345' => 0012345 in other column

So tell me, what the best way to do this?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Roman
  • 431
  • 5
  • 11
  • What is the other application? Ideally, the application should have a way to pass data to Excel along with the format. Excel can't guess the correct format. What you can try to do is, if each column is always defined for the same formatting, formatting the columns first, then pasting only values. – Alisson Bezerra Apr 13 '18 at 19:52
  • Thanks Alisson. The other application is some ugly and unstable product created by Russian programmers related to the environmental calculation. I can't use any alternatives because it is the only app our company should use. I am programmer but I never had a deal with Excel's complex features. I thought that I could write some kind of macros which will guess format based on regular expression or else. Is it possible? – Roman Apr 14 '18 at 03:38
  • @Roman Well you need some criteria to distinguish between the two cases in your example. How can Excel know if you want to format it like the first or the second format (in your example)? – Pᴇʜ Apr 16 '18 at 06:18
  • 1
    @Roman you actually can. Take a look here: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – Alisson Bezerra Apr 16 '18 at 13:13
  • @P I am considering only 2 criteria to set proper cell data format: matching text value to some regular expression (primary) and which column data in (secondary). – Roman Apr 17 '18 at 07:14
  • 1
    Alisson thank you again! That is what I've looking for! – Roman Apr 17 '18 at 07:16

0 Answers0