Как найти пробел в столбце - Исправление недочетов и поиск решений вместе с Examum.ru

Типичная ошибка, которая встречается в электронных таблицах, связана с невидимыми символами пробелов. [1] В ячейке В2 (рис. 1) находится формула, берущая название цвета в ячейке В1 и возвращающая соответствующий код из таблицы D1:E6: =ВПР(B1;D1:E6;2;ЛОЖЬ).

Рис. 1. Формула поиска возвращает цвет, введенный в ячейку В1

Скачать заметку в формате Word или pdf, примеры в формате Excel

В следующем примере формула в ячейке В2 возвращает ошибку, указывая, что значение Красный не было найдено в таблице. Сотни и тысячи пользователей Excel потратили массу времени, чтобы понять, почему подобные операции не работают. Ответ прост: в ячейке D5 написано не «Красный», а «Красный », то есть за словом следует пробел. Для Excel это две разные последовательности символов.

Рис. 2. Формула поиска не может найти в таблице слово «Красный»

Если на рабочем листе тысячи текстовых записей, а вам нужно выполнять сравнения с применением этого текста, то вы, вероятно, захотите найти ячейки с лишними пробелами и исправить их. Под лишними пробелами понимается любое следующее содержимое текстовых записей:

один ведущий пробел и более;
один завершающий пробел и более;
два и более последовательных пробела в самом тексте.

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

Выделите все ячейки с текстом, в которых хотите применить условное форматирование.
Выполните команду Главная –> Условное форматирование –> Создать правило, чтобы открыть диалоговое окно Создание правила форматирования (рис. 3).
В верхней части этого окна выберите параметр Использовать формулу для определения форматируемых ячеек.
В области Измените описание правила введите формулу: =D2<>СЖПРОБЕЛЫ(D2). Данная формула предполагает, что ячейка D2 является верхней левой ячейкой в диапазоне. Если это не так, замените адрес верхней левой ячейки, который вы указали в шаге 1.
Нажмите кнопку Формат, чтобы отобразить диалоговое окно Формат ячеек, и выберите тип форматирования, которое хотите применить к ячейкам, содержащим лишние пробелы, — например, желтую заливку.
Нажмите Ok два раза.

В результате во всем диапазоне, который вы выделили на шаге 1, ячейки с лишними пробелами будут выделены с помощью заданного вами форматирования, и вы сможете легко их найти и исправить (рис. 4).

Рис. 3. Параметры условного форматирования для выделения ячеек, содержащих лишние пробелы

Рис. 4. Условное форматирование выделило ячейки с лишними пробелами

Функция СЖПРОБЕЛЫ действует так, что формула, описанная в шаге 4, также применяет условное форматирование ко всем числовым ячейкам. Если в вашем диапазоне встречаются числа, используйте на шаге 4 формулу: =ЕСЛИ(НЕ(ЕНЕТЕКСТ(D2));D2<>СЖПРОБЕЛЫ(D2)). Кроме того, нужно иметь в виду, что функция СЖПРОБЕЛЫ не удаляет (и не воспринимает при условном форматировании) знак неразрывного пробела.

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 183–185.

Источник

Рис. 1. Формула поиска возвращает цвет, введенный в ячейку В1

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Формула поиска не может найти в таблице слово «Красный»

один ведущий пробел и более;
один завершающий пробел и более;
два и более последовательных пробела в самом тексте.

Выделите все ячейки с текстом, в которых хотите применить условное форматирование.
Выполните команду Главная —> Условное форматирование —> Создать правило, чтобы открыть диалоговое окно Создание правила форматирования (рис. 3).
В верхней части этого окна выберите параметр Использовать формулу для определения форматируемых ячеек.
В области Измените описание правила введите формулу: =D2<>СЖПРОБЕЛЫ(D2). Данная формула предполагает, что ячейка D2 является верхней левой ячейкой в диапазоне. Если это не так, замените адрес верхней левой ячейки, который вы указали в шаге 1.
Нажмите кнопку Формат, чтобы отобразить диалоговое окно Формат ячеек, и выберите тип форматирования, которое хотите применить к ячейкам, содержащим лишние пробелы, — например, желтую заливку.
Нажмите Ok два раза.

Рис. 3. Параметры условного форматирования для выделения ячеек, содержащих лишние пробелы

Рис. 4. Условное форматирование выделило ячейки с лишними пробелами

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. — СПб.: Питер, 2014. — С. 183–185.

Источник

Например, есть список строк, и каждая ячейка содержит несколько слов, разделенных пробелами, есть ли у вас какие-либо методы, чтобы быстро найти положение первого пробела из этих строк, как показано ниже? В этой статье я расскажу, как легко найти положение n-го пространства в Excel.

Найдите положение n-го пространства по формуле

Удалить начальные / конечные / дополнительные / все пробелы из строки

Найдите положение n-го пространства по формуле

Чтобы найти положение n-го пробела, вы можете применить эти формулы.

Найдите позицию первого пробела	= НАЙТИ («»; A1)
Найдите положение второго пробела	= НАЙТИ («»; A1; НАЙТИ («»; A1) +1)
Найдите положение третьего пробела	= НАЙТИ («»; A1; НАЙТИ («»; A1; НАЙТИ («»; A1) +1) +1)
Найдите положение четвертого пространства	= НАЙТИ («», A1, НАЙТИ («», A1, НАЙТИ («», A1, НАЙТИ («», A1) +1) + 1) +1)

Возьмем, к примеру, положение второго пробела в строках.

Выберите пустую ячейку C2, введите эту формулу = НАЙТИ («»; A2; НАЙТИ («»; A2) +1), затем перетащите дескриптор автозаполнения вниз к ячейке, для которой нужна эта формула. Смотрите скриншот:

Удалить начальные / конечные / дополнительные / все пробелы из строки

Но в большинстве случаев вам может потребоваться удалить начальные, конечные, дополнительные или все пробелы из списка строк. В Excel нет встроенной функции, которая может ее решить, но с Kutools for Excel — удобный и мощный инструмент для добавления в Excel, его утилита Remove Spaces может быстро выполнять указанные выше операции по мере необходимости.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите строки, в которых нужно удалить пробелы, и нажмите Кутулс > Текст > Удалить пробелы. Смотрите скриншот:

2. в Удалить пробелы диалог, отметьте опцию в Тип пространства как вам нужно, и вы можете просмотреть удаленный результат в предварительный просмотр раздел. Смотрите скриншот:

3. Нажмите Ok or Применить, теперь из выбранных строк удаляются пробелы.

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (0)

Оценок пока нет. Оцените первым!

Источник

TSQL — how to find if a column has a space char(32)?

select *
from [sometable]
where CHARINDEX(' ', [somecolumn]) > 0

doesn’t work? Any ideas?

FallenAngel

18k15 gold badges82 silver badges113 bronze badges

asked Jul 25, 2009 at 2:22

You have to rtrim CHAR columns.

CHAR columns are padded with spaces on the right up to the maximum length.

RTRIM helps to avoid false positives when storing strings that are shorter than the maximum length.

select * from [table] where rtrim(col) like '% %'



create table dropme
(foo char(32))

insert into dropme values('nospaces')
insert into dropme values('i have a space')
insert into dropme values('space bar')

select replace(foo,' ','|') from dropme
where foo like '% %'

nospaces
i|have|a|space
space|bar

select replace(foo,' ','|') from dropme
where rtrim(foo) like '% %'

i|have|a|space
space|bar

answered Jul 25, 2009 at 2:41

JoeJoe

41.3k20 gold badges104 silver badges125 bronze badges

The following example should illustrate how you can achieve this.

create table #tableTest
(
    someData varchar(100) not null
);

insert into #tableTest(someData) values('dsadsa');
insert into #tableTest(someData) values('fdssf 432423');
insert into #tableTest(someData) values('432423fsdv');
insert into #tableTest(someData) values('321 jhlhkj 543');



select * 
from #tableTest;

select * 
from #tableTest 
where charindex(char(32),someData) > 0;

drop table #tableTest;

answered Jul 25, 2009 at 6:25

John SansomJohn Sansom

40.9k9 gold badges72 silver badges84 bronze badges

What do you mean by «doesn’t work»? Both ways work for me:

SELECT ''''+a+'''' FROM(
SELECT 'asd fgh' AS a UNION ALL
SELECT ' fgh' AS a UNION ALL
SELECT 'asd ' AS a UNION ALL
SELECT 'asfdg') As t
WHERE a LIKE '% %'

---------
'asd fgh'
' fgh'
'asd '

SELECT ''''+a+'''' FROM(
SELECT 'asd fgh' AS a UNION ALL
SELECT ' fgh' AS a UNION ALL
SELECT 'asd ' AS a UNION ALL
SELECT 'asfdg') As t
WHERE CHARINDEX(' ', a) > 0

---------
'asd fgh'
' fgh'
'asd '

answered Jul 25, 2009 at 2:39

A-KA-K

16.7k8 gold badges53 silver badges71 bronze badges

The following worked for me:

PrdPicture LIKE '%'+char(160)+'%'

Martin Evans

45.4k17 gold badges79 silver badges94 bronze badges

answered Jul 6, 2017 at 6:23

PratheeskumarPratheeskumar

4031 gold badge4 silver badges8 bronze badges

Given you’ve not really explained what the problem is…

Are you looking for hardspace (nbsp), CHAR(160)? Or tab CHAR(9)?
These can look like spaces but aren’t

FallenAngel

18k15 gold badges82 silver badges113 bronze badges

answered Jul 25, 2009 at 9:03

gbngbn

420k81 gold badges584 silver badges674 bronze badges

Use below query:

SELECT UniqueName 
FROM employee
WHERE employeeName LIKE '% %'

answered Aug 23, 2018 at 15:53

select * from [sometable] where somecolumn like '% %'

FallenAngel

18k15 gold badges82 silver badges113 bronze badges

answered Jul 25, 2009 at 2:24

KellCOMnetKellCOMnet

1,8393 gold badges16 silver badges27 bronze badges

I had the exact same question as worded, though I was looking to see if the column had a space vs being null. This worked for me:

SELECT UniqueName 
FROM employee
WHERE ascii(employeeName) = 32

The intuitive query doesn’t work; below returns all null rows as well:

SELECT UniqueName 
FROM employee
WHERE employeeName = ' '

answered Apr 30, 2020 at 8:58

Источник

Какой формулой Excel найти последний пробел в ячейке?

Чтобы определить положение последнего пробела в строке надо с помощью формулы массива (для старых версий, начиная с Excel 2007 , вводится через CTRL+SHIFT+ENTER, новые Excel, с 2019 этого не требуют) составить формулу содержащую индекс каждого пробела в строке или ноль для других символов, а затем выбрать элемент с наибольшим значением.

Чтобы выбрать наибольший элемент в массиве значений подходит функция

=НАИБОЛЬШИЙ(массив; номер элемента)

Для самого большого значения (последний пробел) номер элемента равен 1

Для наибольшего значения меньше самого большого (предпоследний пробел) номер элемента равен 2 и так далее.

Чтобы последовательно сделать перебор всех символов строки воспользуемся массивом строк и функцией

=СТРОКА(ссылка на массив строк)

Массив строк можно задать статически для строк, имеющих одинаковую длину (элементы с индексами от 1 до 255):

или динамически (если строка в ячейке A1, элементы с индексами от 1 до последнего):

Получаем такие формулы определяющие индекс последнего пробела строки

Статическая (рассматривает 255 элементов массива)

=НАИБОЛЬШИЙ( ЕСЛИ( ПСТР(A1; СТРОКА( $1:$255); 1)=» «; СТРОКА( $1:$255); 0); 1)

Динамическая (рассматривает с первого до последнего символа строки)

=НАИБОЛЬШИЙ( ЕСЛИ( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР(A1))); 1)=» «; СТРОКА(

Индекс предпоследнего пробела строки вычисляется формулами:

Статическая (рассматривает 255 элементов массива)

=НАИБОЛЬШИЙ( ЕСЛИ( ПСТР(A1; СТРОКА( $1:$255); 1)=» «; СТРОКА( $1:$255); 0); 2)

Динамическая (рассматривает с первого до последнего символа строки)

=НАИБОЛЬШИЙ( ЕСЛИ( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР(A1))); 1)=» «; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР(A1))); 0); 2)

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

Как найти пробелы в Excel

Рис. 1. Формула поиска возвращает цвет, введенный в ячейку В1

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Формула поиска не может найти в таблице слово «Красный»

один ведущий пробел и более;
один завершающий пробел и более;
два и более последовательных пробела в самом тексте.

Выделите все ячейки с текстом, в которых хотите применить условное форматирование.
Выполните команду Главная —> Условноеформатирование —> Создатьправило, чтобы открыть диалоговое окно Созданиеправилаформатирования (рис. 3).
В верхней части этого окна выберите параметр Использовать формулу для определения форматируемых ячеек.
В области Измените описание правила введите формулу: =D2<>СЖПРОБЕЛЫ(D2). Данная формула предполагает, что ячейка D2 является верхней левой ячейкой в диапазоне. Если это не так, замените адрес верхней левой ячейки, который вы указали в шаге 1.
Нажмите кнопку Формат, чтобы отобразить диалоговое окно Формат ячеек, и выберите тип форматирования, которое хотите применить к ячейкам, содержащим лишние пробелы, — например, желтую заливку.
Нажмите Ok два раза.

Рис. 3. Параметры условного форматирования для выделения ячеек, содержащих лишние пробелы

Рис. 4. Условное форматирование выделило ячейки с лишними пробелами

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. — СПб.: Питер, 2014. — С. 183–185.

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

Зачастую при копировании текста или переносе таблиц возникают проблемы с наличием лишних символов, которые могут появиться по самым разным причинам: неправильный ввод данных, импортирование данных из внешних источников и т.д.

Достаточно часто лишние пробелы в Excel встречаются в тексте в следующих случаях:

В начале или в конце предложений;
Дублирование пробелов;
Невидимые символы, в т.ч. неразрывный пробел;
В числах (в разделителях разрядов).

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

Поэтому, чтобы при работе избежать подобных проблем, для корректной работы необходимо удалять лишние символы.

Как удалить пробелы в Excel?

Рассмотрим 2 основных способа, как можно удалить лишние символы:

Инструмент «Найти и заменить». Удаление пробелов через диалоговое окно;
С помощью функций. Формульный вариант удаления.

Инструмент «Найти и заменить»

Предположим, что у нас есть текст или таблица со словами, в котором присутствует дублирование пробелов.

Для начала выделяем диапазон ячеек с данными и выбираем на панели вкладок Главная -> Редактирование -> Найти и выделить -> Заменить либо просто нажимаем сочетание клавиш Ctrl + H:

Появляется стандартное диалоговое окно замены, затем вводим в поле Найти два пробела, а в Заменить на — один. Нажимаем Заменить все и повторяем операцию до тех пор, пока Excel находит повторения.

Как видим алгоритм достаточно простой, однако данный способ удаления пробелов не срабатывает, если они присутствуют в конце или в начале текста.

С помощью функций

В этом случае можно воспользоваться стандартной функцией СЖПРОБЕЛЫ, которая удаляет все пробелы, за исключением одиночных между словами.

Предположим, что у нас имеется список фамилий (столбец A) содержащий лишние пробелы как между словами, так и в начале или конце фамилии. Давайте создадим вспомогательный столбец (столбец B), куда введем формулу СЖПРОБЕЛЫ ссылающуюся на столбец с фамилиями:

Далее копируем содержимое столбца B и вставляем значения без связей в столбец A, после чего удаляем вспомогательный столбец.
Минусом данного способа является его неудобство при работе с большими объемами данных, так как для каждой редактируемой ячейки необходимо создавать дополнительное место где будет обрабатываться исходная ячейка.

Неразрывный пробел в Excel

В случае если после совершенных действий лишние пробелы все же не получилось удалить, то вероятно в тексте могут содержаться неразрывные пробелы.

Данный символ внешне выглядит как обычный пробел, однако его нельзя удалить с помощью функции СЖПРОБЕЛЫ или инструмента «Найти и Заменить» (при вводе в поле для замены обычного пробела).

Как видим в ячейке A2 содержатся лишние пробелы, но функция СЖПРОБЕЛЫ их не удаляет.

Каждый символ в Excel имеет свою кодировку в таблице знаков ANSI (для ОС Windows), в том числе и интересные нам одиночный пробел (символ 32) и неразрывный (символ 160).

Поэтому с помощью функции ПОДСТАВИТЬ заменяем неразрывный пробел (символ 160) на обычный (символ 32), а затем к полученному результату применяя функцию СЖПРОБЕЛЫ получаем требуемый результат:

На самом деле убрать неразрывный пробел можно и с помощью «Найти и заменить», для этого в поле Найти необходимо ввести неразрывный пробел и заменить его либо на обычный, либо удалить, оставив поле Заменить на пустым.

Возникает вопрос, как это сделать?

Первый способ — непосредственно скопировать данный символ из текста и ввести в поле Найти.

Второй способ — воспользоваться Alt-кодом (для систем Windows) и ввести комбинацию Alt + 0160 (удерживая клавишу Alt нужно набрать 0160 на цифровой клавиатуре).

Как убрать пробелы в числах в Excel?

Проблема с удалением пробелов встречается и при работе с числами — в числах попадаются пробелы (к примеру, вместо разделителей разрядов), из-за чего они воспринимаются в Excel как текст.

Предположим, что у нас имеется таблица с числами, где содержатся пробелы. Выделяем диапазон с данными и с помощью инструмента «Найти и Заменить» удаляем пробелы, подставив вместо одиночного пробела пустое поле, как показано в примере выше.

Аналогичный результат можно получить и с помощью формулы:

При этом не забудьте умножить результат на 1, чтобы перевести формулу из текста в число.

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.

Источник