SheetsIQ Tools – Split Text tools Google Sheets

Split By Character

Using this tool, you can split cell text by character. Here are the details:

1 – Split values by characters:

Here you can define how to split the cell values. You can tick one or multiple boxes.

If you tick multiple boxes, then the tool will split values sequentially.

2 – Split values by string:

Here you can mention strings by which the cell value will be splitted. You can specify multiple strings each in one line

3 – Split values by capital letter:

This will split the cell value at each capital letter it finds.

4 – Split values to:

Here you can define whether you want to split cell values into columns or rows.

Split Names

Using this tool, you can split names in cells. Here are the details:

1 – My column has header:

Tick this box if your data has a header row.

2 – Add columns:

Here you can define how many columns you need when you split the names. Whatever boxes you check here, those columns will be added to your sheet. When the tool splits a name, if it finds an appropriate column to fill, it will fill them or leave them blank. For example, if a name does not have “Middle name”, then that cell will be blank for the row.

Split Date & Time

Using this tool, you can split date and time in a cell. Here are the details:

1 – My column has header:

Tick this box if your data has a header row.

2 – Add columns:

Here you can define how many columns you need when you split the cell values. Whatever boxes you check here, those columns will be added to your sheet.

Replace source data – This will replace the source cell value with the result.

Process Data

Fill blank cells

Using this tool, you can quickly fill blank cells in your sheet.

1 – Fill with existing values : 

If you have an existing value in a column or row that you want to fill till a certain cell, you can do this using this tool.

First select a range in a column or row and then choose one of the below options.

You can choose between:

  • Downward – This will fill the data from one cell to all cells in a column of the selected range downwards
  • Upward – This will fill the data from one cell to all cells in a column of the selected range upwards
  • Rightward – This will fill the data from one cell to all cells in a row of the selected range rightwards 
  • Leftward – This will fill the data from one cell to all cells in a row of the selected range leftwards 

2 – Fill with custom value:

Here you can fill a custom value in a selected range. The range can be of any number of rows and columns.

First input the custom value in the text box. Then select a range in the sheet where you want to fill the data. Finally click on the “Fill” button to fill the data.

Text Tools

Add Text By Position

Using this tool, you can quickly add a text in selected cells with advanced options.

First, you have to select a range in your sheet and then use the tool.

1 – Add the following text in the selected cells:

Here you will input the text that you want to add to the cells

2 – Select position in the selected cells:

At the beginning – This will add the text at the beginning of the cell

At the end – This will add the text at the end of the cell

After character number – This will add the text after the given character number as input

Before text – Here you can define a text in the text box and the tool will add the main text before this text.

After text – Here you can define a text in the text box and the tool will add the main text after this text.

Match case – Check this to match the exact case when the tool finds the text (as given in the Before or After text field) in the existing cell value.

3 –  Skip empty cells : This is checked by default which will skip empty cells while adding text.

Remove substring

Using this tool, you can remove a substring or character from a cell value in your range.

First, you have to select a range in your sheet and then use the tool.

1 – Remove substrings individually:

Using this feature, you can remove a substring by specifying the substring in the input box. 

You can check the Match case checkbox which will ensure the case is matched before removing. By default, it does not check the case.

2 – Remove entered characters individually:

Using this feature, you can remove the input characters from a string in the cell.

Add the input characters in the text box. 

If you want to remove comma and line breaks, then check the boxes before them.

Click on the “Remove” button to process the data.

Remove Excess Spaces

Using this tool, you can remove spaces, line breaks, html tags, html entities, non-printing characters in the entire sheet.

You can check one or multiple boxes and click on the “Remove” button to process the data.

The data will be processed sequentially in the order of the checkboxes. 

Remove Character by Position

Using this tool, you can remove characters from a cell value by a predefined character position.

1 – Remove character by position:

Here you can define the start and end of the character position and the tool will remove all characters between these positions from the cell value.

2 – Remove the first/last characters:

This, when checked, will remove the first or last characters from the cell value. You can define the number of characters in the input box.

3 – Remove characters before/after text:

Here you can define a substring and tell the tool to remove everything either “Before” or “After” the substring. 

Match case – If you check this box then the tool while searching for the substring, will try to match the exact case.

Replace Symbols

Using this tool, you can replace certain symbols and codes from the cell value.

You can choose one option at a time and click on the “Modify” button to process the cell value.

Example of replacing codes with symbols:

Marks & Spencers => Marks & Spencers

Polish Text

Using this tool, you can remove extra spaces from cells, add spaces after punctuation marks and change the case to sentence case.

By default all are selected. You can choose to uncheck any one of these. Click on the “Modify” button to process the cells.

Extract

Extract By Strings

Using this tool, you can extract data from cells by specifying a substring. Here are the details:

1 – All after text – If this box is checked, then the all value after the input text will be extracted

2 – All before text – If this box is checked, then the all value before the input text will be extracted

3 – Match case – If checked then while searching the input text above, exact case match will be considered

4 – Place all occurrences to – Here you can choose where to put the extracted text. Either in a separate cell or in one cell

5 – Clear the extracted text from the source data – If checked, then the extracted value will be cleared from the source cell

Extract the first/last N characters

Using this tool, you can extract data from cells by specifying the number of characters from beginning or end. Here are the details:

1 – Extract – Here you can choose if you want to extract the first or last N characters. N is the number input in the input field

2 – Insert new column with the results to the right – If checked, then the tool will create a new column to the right and will place the extracted value in that cell

3 – Clear the extracted text from the source data – If checked, then the extracted value will be cleared from the source cell

Extract Numbers

Using this tool, you can extract numbers from the cell. Here are the details:

1 – Decimal separator – If your numbers has decimal separators (ex: 100.23) then check this box and select from the drop-down options the character that represents the decimal separator

2 – Thousand separator – If your numbers has thousand separators (ex: 10,000) then check this box and select from the drop-down options the character that represents the thousand separator

3 – Place all occurrences to – Here you can choose where to put the extracted text. Either in a separate cell or in one cell

Extract by position

Using this tool, you can extract data from cells by specifying the exact position of the chracters. Here are the details:

1 – Position number of the first character –  This is the starting character from where the value will be extracted

2 – The numbers of chars to extract – Check this box and mention how many characters from the starting character needs to be extracted

3 – Insert new column with the results to the right – If checked, then the tool will create a new column to the right and will place the extracted value in that cell

4 – Clear the extracted text from the source data – If checked, then the extracted value will be cleared from the source cell

Extract Links

Using this tool, you can extract links from cells. Here are the details:

1 – Extract – Here choose what type of link you want to extract

2 – Clear the extracted text from the source data – If checked, then the extracted value will be cleared from the source cell

Clear Data By Type

Using this tool, you can clear data from your sheet by defining the data type. Here are the details:

1 – Clear – Tick the checkbox for each of the data type that you want to clear from the sheet

2 – In addition to the above, you can also clear empty and unused rows and columns

Random Generator

Using this tool, you can generate random values to fill an empty range in your sheet. Here are the details:

1 – Integer – This feature is to generate integers. You can input the from and to values and the tool will generate Integers between these numbers. If you check unique values only, then the tool will generate unique values.

2 – Real – This feature is to generate real numbers. You can input the from and to values and the tool will generate real numbers between these values. If you check unique values only, then the tool will generate unique values.

3 – Boolean – This feature is to generate boolean values. You can either generate TRUE/FALSE or YES/NO type of booleans.

4 – Date – Using this feature you can generate dates between two specified dates.

30+ smart tools to supercharge your sheets