google-sheets
text becomes number after split in google sheets
When I use SPLIT function, the data in my cell gets converted from TEXT to Number (notice the preceding '0' in cell C3 is removed).
is there a way to retain the data as Text after the split (or at least retain the 0 in front) ?
=SPLIT( JOIN("!",B1:B), "!")
Solution
When entering a string that looks like a number, one can keep it as a string by preceding it with a single apostrophe ' (which does not become a part of the string). Same thing works in formulas:
=split(substitute("'" & B1, "!", "!'"), "!")
This appends ' at the beginning and after each separator (which has to be done before splitting). After splitting, the result is as desired: strings, no leading apostrophe.
- working with dates and Calendar: need community help to improve sheet's efficiency
- Calculate the number of times groupings of three zeros (0's) appear in a row
- Remove trailing ".00" from numbers in Google Sheets
- Lambda is not iterated in an Array
- Google Sheets - Publish to Web not working
- Automatic subtraction according to conditions in Google Sheets
- cannot select active spreadsheet
- Filtering data from other google Spreadsheet sheet based on a combo value from other sheet in the same spreadsheet
- Cannot reassign a global variable within an onEdit function
- How to get a list of all sheet names that contain "week of" that updates on edit
- Multiple regex matches in Google Sheets formula
- google sheets script: display cell addresses of color filled cells
- Count occurrences of given character per cell
- How to List All Combinations of Values from Two Semicolon-Separated Columns in Excel/Sheets?
- Appscript cant connect to firebase via API
- Insert Form Responses Value to Table In Google DOC
- Error while converting data from Excel to CSV using SheetJS Library and Google Apps Script
- How to prefill Google form checkboxes?
- Google Apps Script - Remove duplicates in large spreadsheets
- IMPORTXML Google Sheets Formula not working
- Count value in cells above
- How to count number of non-blank cells (cells that contain text)?
- Is it possible to have a multi select list box in Google sheet?
- Getting and Setting Cell Pixel Width and Height of Google Sheet with Apps Script
- Using Lookup formula in URLs to match broad matches
- Return one value from a report list, not sum or record count in Sheets
- Fastest way to search for a row in a large Google Sheet using/in Google Apps Script
- How to make rept formula work in google sheets?
- Google Sheets: Apply lower() to an entire range of values?
- Find the sum of a specific code in each tabs