Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-27-2019, 09:19 PM
ganesang ganesang is offline How to split values from carriage-return from the cell Windows XP How to split values from carriage-return from the cell Office 2016
Competent Performer
How to split values from carriage-return from the cell
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default How to split values from carriage-return from the cell

Hi folks,

I need the help to split values from cell values which separated using carriage-return/hard return as follows:

for instance, the cell contains like,

case 1: case 2:

cell K: cell L: or cell K: cell L:
abcd123785 53618 abcd123785 53618
bnsertsg1237 24515 bnsertsg1237

I need the conversion in other cell like,

case 1:


<opt mfr="53618" pnv="abcd123785"/>
<opt mfr="24515" pnv="bnsertsg1237"/>

case 2:
<opt mfr="53618" pnv="abcd123785"/>
<opt mfr="" pnv="bnsertsg1237"/>

Please help me out on this...

Thanks
ganesan. g
Attached Files
File Type: xlsx sample.xlsx (8.8 KB, 8 views)

Last edited by ganesang; 11-27-2019 at 09:24 PM. Reason: attachment added
Reply With Quote
  #2  
Old 11-28-2019, 12:39 PM
NBVC's Avatar
NBVC NBVC is offline How to split values from carriage-return from the cell Windows 10 How to split values from carriage-return from the cell Office 2016
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Try:


="<opt mfr="""&LEFT(L5,FIND(CHAR(10),L5)-1)&""" pnv="""&LEFT(K5,FIND(CHAR(10),K5)-1)&"""/>" &CHAR(10)&"<opt mfr="""&MID(L5,FIND(CHAR(10),L5)+1,255)&"""pnv="& MID(K5,FIND(CHAR(10),K5)+1,255)&"""/>"


copied down


Format the result cells by Wrapping text
Reply With Quote
  #3  
Old 11-28-2019, 11:26 PM
ganesang ganesang is offline How to split values from carriage-return from the cell Windows XP How to split values from carriage-return from the cell Office 2016
Competent Performer
How to split values from carriage-return from the cell
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Thanks for the reply

Can you please help me more on this.... as follow


if i have the data like

in cell k have three values and cell l one value how can call cell l value to all cell k values

"<opt mfr=""09021"" pnv=""RK73H2HL **4991F""/>
<opt mfr=""09021""pnv=CRCW20104K99FKTF""/>"

also i have the values in cell K sometimes more than one, in this case will take cell l value
Reply With Quote
  #4  
Old 12-02-2019, 07:16 AM
NBVC's Avatar
NBVC NBVC is offline How to split values from carriage-return from the cell Windows 10 How to split values from carriage-return from the cell Office 2016
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Can you post the sample with expected results in an excel file?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
remove carriage return abenitez77 Excel Programming 2 04-04-2019 11:41 PM
carriage return on PPT yangthomson PowerPoint 0 04-21-2016 07:16 PM
Inserting Carriage Return rsrasc Word VBA 4 02-15-2015 11:23 PM
How to split values from carriage-return from the cell Reference another workbook and return values from the same cell across multiple sheets/tabs lwls Excel Programming 5 11-08-2014 02:11 PM
Carriage Return Help UCHelp Word 1 04-04-2010 10:11 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:00 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft