Text to Column Help

yashavantharaja_thim

Hi i am having data in excel sheet following format.. i want to split into two columns can you help me

Column

C232 Incubes ltd

C534 Gnet LT

Broadband,inc.

Out put should be in following format

column1 column2

C232 Incubes ltd

C534 Gnet LT

Broadband,inc.

Output

Accepted answers

All comments

Emil_Kos

Hi @yashavantharaja_thim,

I have used text to column + formula tool in order to make it work.

The output:

Please mark my post as a solution if this was helpful!

yashavantharaja_thim

Thanks Emll_Kos, i split the column into column1 and column2 using Test to column ,but in formula column how to combine .. i am new user.

using test to column i am getting below

column column1 column2

C232 Incubes ltd C232 Incubes ltd

C534 Gnet Lt C534 Gnet Lt

Boradband,inc. Boradband, inc.

vizAlter

Hi @yashavantharaja_thim — Try this solution:

FindString([Column], " ") function will identify the 1st position of the Space:

Left([Column], FindString([Column], " "))

IF FindString([Column], " ") < 1 THEN 
	Null() 
ELSE 
	Trim(Right([Column], Length([Column])-FindString([Column], " "))) 
ENDIF

Text to column__vA.yxmd

BretCarr

It looks as though you may want to use REGEX to parse your data into the columns since you seem to have a customerID that appears from time to time but not always?

If you do a REGEX on your data, choose method PARSE and use the following REGEX pattern code:

^(C\d{3})?\s?(.*)$

Does that do the trick? Are there more variants in your data that this doesn’t account for?

REGEXing has become a hobby of mine so let me know!

If this helps, don’t forget to mark it as a solution.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703