CODEWITHSUNDEEP
excelgoogle-sheetsregular-language
Kate KAte
Kate KAte

Reputation: 9

Divide text in the column into two columns (text and numbers) - Google sheet or Excel

I have a document in google sheets and the column consists of the name and version, like NLog.Config.4.3.0, NLog.Config.4.4.9 and so on. See the image below for other examples.

I need to divide this into two columns - name and version, but I'm not familiar with regular expressions so close that I can get this info.

I can use excel and then import it to the Google doc, it doesn't matter for me how to do that.

enter image description here

Upvotes: 0

Views: 98

Answers (3)

Erik Tyler
Erik Tyler

Reputation: 9345

Supposing that your raw data were in A2:A, place this in B2:

=ArrayFormula(IFERROR(REGEXEXTRACT(A2:A,"(\D+)\.(.+)"),A2:A))

The regular expression reads "Extract any number of non-digits up to but not including a period as group one, and everything remaining into group two." (In other words, "As soon as you run into a digit after a period, start group two.")

The IFERROR clause means, "If this pattern can't be found, just return the original cell data."

Upvotes: 1

Aresvik
Aresvik

Reputation: 4620

Assuming your content is in column A (Google Sheets), try this arrayformula in any cell other than column A:

=arrayformula(iferror(split(REGEXREPLACE($A:$A,"(\.)(\d+.+$)",char(6655)&"$2"),char(6655)),))

There are two regex groups denoted in ():

(\.) and (\d+.+$).

The first group looks for a dot . - it's escaped using \. The second group looks for a number (0-9) \d, one or more occurrences + then ending with $ one or more + of any character ..

The replacement is char(6655) (wouldn't usually be found in your dataset), and the contents of group two $2.

Then the split function divides the text into two columns by the char(6655) character.

iferror returns nothing if nothing is split.

The arrayformula works down the sheet.

Upvotes: 0

Jayvee
Jayvee

Reputation: 10875

You can try something like this:

Suppose you have your string in A1, then in B1 you can enter this:

=LEFT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))

and in C1 this:

=RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)

you may need to do some adjustments if there are cases without numbers as it will produce an error, for example you can round it with an Iferror like this:

=IFERROR(LEFT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))),A1)

Note: A1&"0123456789" is a 'trick' to avoid the search to return error, as the search is looking for all the numbers in the array; we just need the position of the first one, thus the MIN().

Upvotes: 1

Related Questions