2

У интернет-брокера, которым я пользуюсь, должен быть самый неблагополучный сайт в мире! Они не предоставляют функцию "экспорта", вместо этого сообщая своим пользователям, что они могут просто скопировать данные из таблиц, отображаемых в их браузере, и вставить их в Excel. (В самом деле!)

Удивительно, но Excel (2016, для Mac) может быть преобразован в "положительные текстовые числа" в реальные числа, которыми можно управлять с помощью числовых функций (обычно SUM ). Тем не менее, "отрицательные текстовые числа" бросили вызов всем усилиям, которые я нашел, чтобы преобразовать в отрицательное число.

"Положительный текстовый номер": 123,45 $
"Отрицательное текстовое число": - $ 543,21 (обратите внимание на начальный текстовый hyphen)

РЕДАКТИРОВАТЬ:
"Отрицательное текстовое число": - $ 299,55 (Обнаружено, что начальный символ НЕ является hyphen ; это может быть em dash
Мои извинения за путаницу ... Теперь мне интересно, какие программисты могли бы использовать em dash вместо minus sign/hyphen на веб-странице, полной цифр, но это вопрос для моего онлайн-брокера.

Я могу преобразовать "положительное текстовое число" в ячейку A1 следующим образом: =VALUE(RIGHT(A1,6))

Но это не работает с "отрицательным числом текста". Он получает правильное число, но теряет отрицательный знак. Я пытался изменить значение chars , но это не помогает.

Я также попробовал следующее без успеха:
1. умножение на 1
2. текст в столбцы
3. форматировать ячейки ...

Есть ли способ сделать это - кроме повторного ввода вручную?

|источник

2 ответа2

1

Если ячейка A1 содержит текст в формате «$ 123,45» или «- $ 543,21», вы можете использовать следующую формулу, чтобы получить числовое значение.

=IF(LEFT(A1,1)="-",0-VALUE(RIGHT(A1,LEN(A1)-1)),VALUE(A1))

Если текст начинается с «-», он получит значение всего после «-» и сделает его отрицательным.

|источник
0

Я бы порекомендовал:

  1. Извлечь значение после знака $ , не использовать фиксированную позицию
  2. Проверьте знак значения, проверив символ непосредственно перед знаком $ .

Таким образом, формула может быть

=IF(IF(ISERROR(FIND(MID(A1,FIND("$",A1)-1,1),"<dash><hyphen>")),3,FIND(MID(A1,FIND("$",A1)-1,1),"<dash><hyphen>"))<3,-MID(A1,FIND("$",A1)+1,LEN(A1)),0+MID(A1,FIND("$",A1)+1,LEN(A1)))

где

"<dash><hyphen>" - литерал с возможными отрицательными знаками (замените заполнители на соответствующие символы в любом порядке, чтобы в указанном случае вы получили 2-символьный литерал)

3 - число, которое больше числа символов в предыдущем литерале

Эти значения могут быть заменены ссылкой на ячейку, которая содержит все возможные знаки "минус" (например, Z1 и 1+LENGTH(Z1) соответственно).

Обновить. Отладка по формуле.

Создайте таблицу на пустом листе.

Вставьте 4 различных значения в столбец A (без / с начальными пробелами, положительные / отрицательные).

Вставьте следующие формулы:

Cell          Formula                      Meaning
-------------------------------------------------------------
$B$1          =IF(I1,-G1,0+G1)             Result
$C$1          =LEN(A1)                     Data length in source
$D$1          =FIND("$",A1)                $ position in source
$E$1          =MID(A1,D1-1,1)              symbol before $
$F$1          =FIND(E1,"<dash><hyphen>")   symbol before $ position in hyphens list
$G$1          =MID(A1,D1+1,C1)             number from source
$H$1          =ISERROR(F1)                 correction when no symbol before $ in list
$I$1          =IF(H1,3,F1)<3               define is a source positive or negative

Скопируйте этот пакет формул в строки 2-4. Проверьте, что вы получаете вывод, как это:

     A        B      C   D   E      F       G    H       I
1    $123     123    4   1   #VAL!  #VAL!   123  TRUE    FALSE   
2    -$234    -234   5   2   -      1       234  FALSE   TRUE    
3      $345   345    6   3          #VAL!   345  TRUE    FALSE   
4      -$456  -456   7   4   -      1       456  FALSE   TRUE

Столбцы CI содержат отдельные этапы расчета, а столбец B содержит окончательную формулу со всеми подстановками.

Где сбой расчета?

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .