CODEWITHSUNDEEP
javascriptgoogle-sheets
Rodney Hatfield
Rodney Hatfield

Reputation: 35

How to separate text within a cell of a google sheets by Capital Letter

I need help splitting the text that I have in a cell within my Google Sheets spreadsheet. The data I have is missing a space between each word that starts with a capital letter. For example Cell A1 contains "BarbaraSmith" I need a google script that will separate the contents in A1 to B1 and C1 where B1 will contain Barbara and C1 will contain Smith.

Thanks

Upvotes: 1

Views: 720

Answers (2)

Ravi Teja
Ravi Teja

Reputation: 161

Check this link to see how to create a custom function https://developers.google.com/apps-script/guides/sheets/functions#creating_a_custom_function

Copy this code into that custom function

function CSepString(from) {
 var patt = new RegExp("[A-Z][^A-Z]*", "g");
 return [from.match(patt)];
}

come to the google sheet and use it like this 

=CSepString(A1)

Upvotes: 1

Siva Kondapi Venkata
Siva Kondapi Venkata

Reputation: 11001

Use for loop, check if letter is capital, then move all chars collected into words array.

function splitWords(str) {
  const start = "A".charCodeAt(0);
  const end = "Z".charCodeAt(0);
  const words = [];

  let word = "";
  for (let i = 0; i < str.length; i++) {
    const code = str.charCodeAt(i);
    if (code >= start && code <= end) {
      if (word) {
        words.push(word);
        word = "";
      }
    }
    word = `${word}${str[i]}`;
  }
  words.push(word);
  return words;
}

console.log(splitWords("BarbaraSmith"));

Upvotes: 1

Related Questions