Forum Discussion
Issue with Separating data
Good morning,
I have an issue with data that we export from our Sage accounts into Excel. It comes into Excel from a single continuous text box entry in Sage and it is a description of a rubber hose assembly as follows :
T350-LE (Blue) Hot Water and Saturated Steam
Delivery Hose
Terminations: Flex-Rite Series 7000 permanently attached
couplings comprising:
Hose size: 1/2 inch
End 1: 1/2 in S/S BSP Nut & 316S/S 60 Deg Cone Seat
End 2: 1/2 in 316S/S BSP Taper Male.
Overall length: 1 Metres
Each of the above in BOLD represents how we need to separate the data into columns headed with those titles.
I hope this makes sense !
10 Replies
- if possible，share some dummy data and expected result。
peiyezhu Hi, see attached. The text all in one column needs to be separated out into columns headed as per the bold text in my post.
if possible,share excel file rather than pdf.
In fact,I can not get what you want excatly by now because the pdf did not show all datas completely.
Below is a reference to split columns.
hope helpful.
https://techcommunity.microsoft.com/t5/excel/text-after-delimiter/m-p/3832003
I'm not clear on what you're looking to do for the first column but this may do it for you:
=LET(split,TEXTSPLIT(A1,":",CHAR(10),,,""),WRAPCOLS(TOCOL(split),2))- Hi there, thanks for this !
Yes, I wasn't very clear - Column 1 should be Hose Type and in this case, that type is "Steam Delivery Hose"
D
