Separating text and links from a hyperlink in OpenOffice

Question

I have a list of bookmarks which is converted into an OpenOffice spreadsheet. Now I want to separate the link URL (in blue below) and its text (in black) separately into two columns.

Is there any method to achieve this?

Answer 1

The key is to use the Text Import functionality in OpenOffice.org Calc.

Without having a copy of your bookmarks to see the precise formatting used, I will provide general instructions that you may need to tweak to conform to your specific formatting.

The easiest way to perform this action is to open your list of bookmarks in a text editor, and copy the entire file to the clipboard.

Now create a new spreadsheet in OpenOffice.org Calc.

In OpenOffice.org, press Ctrl+Shift+V to perform a Paste Special action. After specifying that you want to import the clipboard as unformatted text, you will be presented with the Text Import dialog box:

In that dialog box, select the Separated by radio button. Depending on the exact formatting of the source data, adjust the checkboxes below Separated by to properly separate the link URL from the text. If you have any challenges, you can enter different characters into the text field to the right of the Other checkbox. Entering a forward slash (/) into that text field will find all URLs that begin with http:// and https:// and put them into their own column.

Answer 2

Since it seems it misses a direct function to extract the hyperlink from the text, it can be a quick solution to use a macro that just exists. In the bottom of the answer you will find the code that defines a function CELL_URL(SheetNumber,Row,Column) able to return the value of the hyperlink. After that you will add that macro you will be able to use the function CELL_URL (below the instructions).

Simple solution. Following the example of your picture, in the cell B90 you can write =CELL_URL(1,ROW(A90),1). This will write in B90 the link of the cell A90, else it will write nothing. In the cell C90 you can simply write =A90 to have only the text (without hyperlink). So you split text and hyperlink. After you can copy these cells (B90 and C90) and paste to all your column B and C, or on all the rows you need.
Note the A column is fixed in your example and for this I wrote =CELL_URL(...,1). You can specify a different number of the column if you need, or you can call this function from another sheet.

Extended solution. Since it was unclear (for me) if you want simply to split text and hyperlink of the even cells (2,4,6...), or if you want instead to rise the odd cells (3,5,...) contents close to the even ones, I propose the following schema that will give you in the B column the text, in the C the address and in the D one the url. One line will be filled in the column B,C and D, one will be empty. (After you can copy, paste for value in another sheet and reorder to skip the empty lines, or you can add a more complex equation as parameter of the function directly in another sheet).

I assume you are working on the 1st sheet of calc, and the text is in the column A starting from the row 2 so that in A2 there is the first case. If not shift the numbers from A2, for example to A20, from A3 to A21, from B2 to B20...

• In the cell B2 write =IF(D2="","",A2).
• In the cell C2 write =IF(D2="","",A3)
• In the cell D2 write =CELL_URL(1,ROW(A2),1)

Copy the three cells and paste where you need.
The logic is: if it is able to extract the link (D is filled) it writes the other columns (B and C).

How to add the macro

Go to Tools->Macro->Organize Macros->Open/Libre Office Basic from the sheet you're working on, create a new Macro. Give it a name that you like. You'll see a new window opening. Copy paste the code.

---

The macro

REM  *****  BASIC  *****
REM ################### RETURNING STRING #################################################
Function CELL_NOTE(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns annotation text
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_NOTE = v.Annotation.getText.getString
   else
      CELL_NOTE = v
   endif
End Function
Function CELL_URL(vSheet,lRowIndex&,iColIndex%,optional n%)
'calls: getSheetCell
REM returns URL of Nth text-hyperlink from a cell, default N=1)
Dim v
   If isMissing(n) then n= 1
   If n < 1 then
      CELL_URL = Null
      exit function
   endif
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      if v.Textfields.Count >= n  then 
         CELL_URL = v.getTextfields.getByIndex(n -1).URL 
      else
         Cell_URL = Null
      endif
   else
      CELL_URL = v
   endif
End Function
Function CELL_FORMULA(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM return unlocalized (English) formula
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_FORMULA = v.getFormula()
   else
      CELL_FORMULA = v
   endif
End Function
Function CELL_STYLE(vSheet,lRowIndex&,iColIndex%,optional bLocalized)
'calls: getSheetCell
REM return name of cell-style, optionally localized
Dim v,s$,bLocal as Boolean
   if not isMissing(bLocalized) then bLocal=cBool(bLocalized)
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      if bLocal then
         s = thisComponent.StyleFamilies("CellStyles").getByName(v.CellStyle).DisplayName
      else
         s = v.CellStyle
      endif
      CELL_STYLE = s
   else
      CELL_STYLE = v
   endif
End Function
Function CELL_LINE(vSheet,lRowIndex&,iColIndex%,optional n)
'calls: getSheetCell
REM Split by line breaks, missing or zero line number returns whole string.
REM =CELL_LINE(SHEET(),1,1,2) -> second line of A1 in this sheet
Dim v,s$,a(),i%
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      s = v.getString
      if not isMissing(n) then i = cInt(n)
      if i > 0 then
         a() = Split(s,chr(10))
         If (i <= uBound(a())+1)then
            CELL_LINE = a(i -1)
         else
            CELL_LINE = NULL
         endif
      else
         CELL_LINE = s
      endif
   else
      CELL_LINE = v
   endif
end Function

REM ################### RETURNING NUMBER #################################################
Function CELL_ISHORIZONTALPAGEBREAK(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_ISHORIZONTALPAGEBREAK = Abs(cINT(v.Rows.getByIndex(0).IsStartOfNewPage))
   else
      CELL_ISHORIZONTALPAGEBREAK = v
   endif
End Function
Function CELL_ISVERTICALPAGEBREAK(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_ISVERTICALPAGEBREAK = Abs(cINT(v.Columns.getByIndex(0).IsStartOfNewPage))
   else
      CELL_ISVERTICALPAGEBREAK = v
   endif
End Function
Function CELL_CHARCOLOR(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns color code as number
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_CHARCOLOR = v.CharColor
   else
      CELL_CHARCOLOR = v
   endif
End Function
Function CELL_BACKCOLOR(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns color code as number
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_BACKCOLOR = v.CellBackColor
   else
      CELL_BACKCOLOR = v
   endif
End Function
Function CELL_VISIBLE(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns visibility state as number 0|1
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_VISIBLE = Abs(v.Rows.isVisible)
   else
      CELL_VISIBLE = v
   endif
End Function
Function CELL_LOCKED(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns locked state as number 0|1
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_LOCKED = Abs(v.CellProtection.isLocked)
   else
      CELL_LOCKED = v
   endif
End Function
Function CELL_NumberFormat(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns the number format index
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_NumberFormat = v.NumberFormat
   else
      CELL_NumberFormat = v
   endif
End Function
Function CELL_NumberFormatType(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM return a numeric com.sun.star.util.NumberFormat which describes a format category
Dim v,lNF&
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      lNF = v.NumberFormat
      CELL_NumberFormatType = ThisComponent.getNumberFormats.getByKey(lNF).Type
   else
      CELL_NumberFormatType = v
   endif
End Function

'################### HELPERS FOR ABOVE CELL FUNCTIONS #########################################
Function getSheet(byVal vSheet)
REM Helper for sheet functions. Get cell from sheet's name or position; cell's row-position; cell's col-position
on error goto exitErr
   select case varType(vSheet)
   case is = 8
      if thisComponent.sheets.hasbyName(vSheet) then
         getSheet = thisComponent.sheets.getByName(vSheet)
      else
         getSheet = NULL
      endif
   case 2 to 5
      vSheet = cInt(vSheet)
      'Wow! Calc has sheets with no name at index < 0,
      ' so NOT isNull(oSheet), if vSheet <= lbound(sheets) = CRASH!
      'http://www.openoffice.org/issues/show_bug.cgi?id=58796
      if(vSheet <= thisComponent.getSheets.getCount)AND(vSheet > 0) then
         getSheet = thisComponent.sheets.getByIndex(vSheet -1)
      else
         getSheet = NULL
      endif
   end select
exit function
exitErr:
getSheet = NULL
End Function

Function getSheetCell(byVal vSheet,byVal lRowIndex&,byVal iColIndex%)
dim oSheet
'   print vartype(vsheet)
   oSheet = getSheet(vSheet)
   if varType(oSheet) <>9 then
      getSheetCell = NULL
   elseif (lRowIndex > oSheet.rows.count)OR(lRowIndex < 1) then
      getSheetCell = NULL
   elseif (iColIndex > oSheet.columns.count)OR(iColIndex < 1) then
      getSheetCell = NULL
   else
      getSheetCell = oSheet.getCellByPosition(iColIndex -1,lRowIndex -1)
   endif
End Function

---

References:

• Introspective cell functions, on openoffice forum.
• Another answer on StackOverflow.
• Further reading on Expert Exchange.
• Further inspiration from Ubuntu forum to start to write your own macro.

Working on Libreoffice Version: 4.2.8.2.

Answer 3

I am not a user of OpenOffice calc, but in view of the other complicated answers, I will try to give a simpler solution (which might even work).

If I understood correctly, you currently have a one-column spreadsheet where A1, A3, A5 etc. contain text with links, and where A2, A4, A6 etc. contain simple text.

You would like to construct a new spreadsheet like this :

A1 , A2  
A3 , A4  
A5 , A6  
etc.

My idea is to add a column B1 with the formula =MOD(ROW();2), which you propagate to the whole B column. This will give you:

A1 , 1
A2 , 0
A3 , 1
A4 , 0
etc.

Now sort (or filter if sort does not work as needed) by column B to aggregate together all the columns :

A1 , 1
A3 , 1
etc.
A2 , 0
A4 , 0
etc.

You can now copy-paste the A column where B=1 to a new spreadsheet in column A, then copy-paste to the new spreadsheet in column B the A column from the old spreadsheet where B=0.

This should give the desired result (I hope).