Пусть формула обрабатывает значение как текст, а не как число

Question

У меня есть формула Excel для чтения данных из столбца. Данные в этом столбце иногда имеют формат даты, такой как "10-11". Несмотря на то, что я обеспечил, чтобы столбец был отформатирован в тексте - и все значения правильно отображались в виде обычного текста, а не интерпретировались как даты, - формула в основном переосмысливает их как даты в ссылке.

Мне нужен способ заставить ссылку на ячейку формулы интерпретировать ячейку как текст. Я попытался TEXT(A1, "@"), но он не работает - он дает числовое значение даты.

Answer 1

Ответ Брайана Камира объясняет почему, но вот функция рабочего листа, которая будет работать для вас. Обратите внимание, что вы должны создать этот числовой массив в нем в зависимости от длины самой длинной строки. Это формула массива, поэтому, когда вы впервые вводите ее, вы должны нажать CTRL-SHIFT-ENTER, а затем нажать и перетащить ее вниз по столбцу.

=LEFT(A1, MATCH(FALSE, ISNUMBER(VALUE(MID(A1, {1,2,3,4,5}, 1))),0) - 1)

Answer 2

Краткий ответ. При ссылке на числовые (или на даты) текстовые значения в формуле не используйте их в тех местах формулы, где Excel ожидает число.

Длинный ответ: даже если исходный столбец отформатирован как текст, а значения в исходном столбце действительно введены как текст (а не числа, включая даты), Excel может автоматически преобразовывать текстовые значения в числа (включая даты), когда вы ссылаетесь на них в формула, если вы используете их в месте, где Excel ожидает число (или дату).

Например (при условии форматов даты в США), на пустом листе:

1. Установите для столбца A формат текста.

2. В ячейку A1 введите значение 10-11.

3. В ячейку B1 введите формулу =T(A1). Функция рабочего листа T () возвращает предоставленное значение, если оно является текстом. В противном случае он возвращает пустую строку. Результат формулы в ячейке B1 должен быть 10-11, указывая, что значением A1 является текст, а не число или дата (в этом случае результатом будет пустая строка).

4. В ячейку C1 введите формулу =A1.

5. В ячейку D1 введите формулу =T(C1). Результат также должен быть 10-11, указывая на то, что значением формулы в С1 является текст, а не число или дата. Это показывает, что вы можете (иногда) использовать текстовое значение, которое выглядит как число (или дата) в формуле, и Excel может воспринимать его как текст (что вам и нужно).

6. В ячейку E1 введите формулу =A1+0. Результат будет 40827. Это числовое значение даты 11 октября 2011 г. Это показывает, что вы можете (иногда) использовать текстовое значение, которое выглядит как число (или дата) в формуле, и Excel автоматически преобразует его в число (это то, что вы заметили), если вы используете его в месте (например, по обе стороны от оператора +), где Excel ожидает число.

Answer 3

Чтобы вставить значение в ячейку, чтобы оно не было автоматически отформатировано и воспринималось как текст, вы можете ввести его следующим образом:

=("cell data")

например:

=("[phone]")

Answer 4

Когда вы используете &"" Excel преобразует результаты формулы в текст (например, *1 преобразовал бы в числа).

Таким образом, вы можете использовать эту формулу:

=TEXT(A1;"jj-mm")&""

Answer 5

Если вы поместите одну кавычку перед текстом в ячейке, она должна быть представлена ​​в виде текста на всех ссылках:

'10-11

Answer 6

Просто добавьте ноль к входу!

У меня была похожая проблема, когда у меня был список чисел с префиксом текста (например, FOO-1, FOO-25, FOO-979), но я просто хотел номерную часть (1, 25, 979), чтобы я мог запустить другой формула от этого. Я использовал SUBSTITUTE, чтобы заменить текстовую часть пробелом, но моя другая формула, использующая эти числа, давала фиктивные результаты. В итоге я сделал свою формулу так:

= ЗАМЕНА (B1:B10,"FOO-","")+0, и теперь ISNUMBER говорит TRUE, где раньше он говорил FALSE.

Answer 7

В моем случае у меня есть бланк формы, который дилеры используют для рекламы деталей, и он рассчитывает окончательную стоимость; он ссылается на заблокированный лист "продукты". Проблема в том, что у меня не было возможности контролировать то, что они вошли.

Products can be like:
101 = A true number
7-2009 = Reads as date
7-5601-RT = TEXT/NUMBER reads as both number or text (NOT SOLVED YET)
CP6072CD = reads as plain text

Я понял большую часть этого; единственный, который не является тем, который читается как текст / номер.

Если кто-то ищет подобное решение, я сделал следующее: я создал три дополнительных столбца, чтобы протестировать и отобразить три разных случая: "NUMBER", "DATE", "TEXT".

DATE:         =NOT(ISERROR(DATEVALUE(B42)))
ISOTHER:      =(ISNUMBER(--(MID(B42,ROW(INDIRECT("1:"&LEN(B42))),1))))
NUMBER:       =ISNUMBER(B42)
NUMBER/TEXT:  =NOT SOLVED YET

I42 = DATE
J42 = NUMBER
K42 = OTHER
L42 = TEXT
M42 = THE RESULT OF THE QUERY BELOW

=IF(AND(I42 = FALSE, J42 = FALSE, K42 = TRUE), "NUMBER", IF(AND( I42 = TRUE, J42= FALSE, K42=TRUE), "DATE", "TEXT"))

Это (ВЫШЕ) проверяет все истинные / ложные результаты и в зависимости от того, какое значение оказывается, я форматирую каждый из них, используя:

ISNUMBER = VALUE(B42)
DATE FORMATTED AS B42*1
ELSE TEXT - AS ORIGINAL

=IF(M42 = "NUMBER", VALUE(B42), IF(M42 = "DATE", B42*1, B42))

Так что теперь мне просто нужно выяснить, как проверить, является ли что-то и текстом, и числом, потому что 7-5601-RT проверяет то же самое, что и число: "ЛОЖЬ, ЛОЖЬ, ИСТИНА, ИСТИНА"